SQL Update Statement using inner Join condition

Disha 0
Viewed 57

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')
sql-update oracle sql
2 Answers

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'))
MintBerryCRUNCH0

Thanks everyone for your valuable inputs. The solution provided by MintBerryCRUNCH worked. Thank you so much MintBerryCRUNCH

For Oracle DB, below is the query:

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')
)
Disha0
Related