SQL Update Statement using inner Join condition

Question

I am trying to update a column in a table CON for specific records based on the value from another table. So I need to use a join condition in update. I tried the below query but it does not work. Please help.

update S_CONTACT
SET CON.ACTIVE_FLG = 'N'
from S_CONTACT CON
INNER JOIN S_USER USR
ON CON.PAR_ROW_ID= USR.PAR_ROW_ID
where USR.LOGIN in('BJAME','GWOOD','HTRAME')

Answer 1

You do not need the alias on the set. You are already stating that you are updating S_CONTACT. This answer is for SQL-Server as you appear to be using syntax that is product specific. This syntax isn't valid in Oracle.

UPDATE S_CONTACT
SET ACTIVE_FLG = 'N'
FROM S_CONTACT CON
JOIN S_USER USR
ON CON.PAR_ROW_ID= USR.PAR_ROW_ID
WHERE USR.LOGIN in('BJAME','GWOOD','HTRAME')

For Oracle you can try the below:

UPDATE S_CONTACT
SET S_CONTACT.ACTIVE_FLG = 'N'
WHERE S_CONTACT.PAR_ROW_ID IN
        (SELECT CON.PAR_ROW_ID
        FROM S_CONTACT CON
        INNER JOIN S_USER USR
        ON CON.PAR_ROW_ID= USR.PAR_ROW_ID
        WHERE USR.LOGIN in('BJAME','GWOOD','HTRAME'))