pl/sql 12C - MERGE INTO output error Columns referenced in the ON Clause cannot be update IU.LOGIN-CodePudding

I don't understand my problem in pl/sql 12c with the following code:

MERGE INTO ident_utilisateur iu
USING users us
ON ((regexp_substr (iu.LOGIN, '[^_] ', 1)) = TO_CHAR(us.cnbf))
WHEN MATCHED THEN 
UPDATE
SET
iu.login = (regexp_substr (iu.login, '[^_] ', 1)),
iu.email_contact = (regexp_substr (iu.email_contact, '[^_] ', 1)),
iu.valide = 1,
iu.date_fin = ''
WHERE iu.login LIKE '%_LOG_%' 
AND iu.login NOT LIKE '%AUT_LOG%'

(iu.LOGIN actually looks like "169_LOG_04/10/2022", the regex output "169" AND us.cnbf looks like "169" )

this code expected error Columns referenced in the ON Clause cannot be update IU.LOGIN

For info: this code work but it don't JOIN the users' table

update ident_utilisateur iu
set
iu.login = (regexp_substr (iu.login, '[^_] ', 1)),
iu.email_contact = (regexp_substr (iu.email_contact, '[^_] ', 1)),
iu.valide = 1,
iu.date_fin = ''
--FROM ident_utilisateur iu
--INNER JOIN users us ON (regexp_substr (iu.login, '[^_] ', 1)) = TO_CHAR(us.CNBF)
WHERE iu.login LIKE '%_LOG_%'
AND iu.login NOT LIKE '%AUT_LOG%'

if i uncomment the From and INNER line it output SQL command not properly ended

Really thanks for ur helps!

Thery

CodePudding user response：

Try this:

update ident_utilisateur iu
set
iu.login = (regexp_substr (iu.login, '[^_] ', 1)),
iu.email_contact = (regexp_substr (iu.email_contact, '[^_] ', 1)),
iu.valide = 1,
iu.date_fin = ''
WHERE iu.login LIKE '%_LOG_%'
AND iu.login NOT LIKE '%AUT_LOG%'
AND exists (select null from users us where regexp_substr (iu.login, '[^_] ', 1) = TO_CHAR(us.CNBF))