SQL Update a table based on join with anther table

sql oracle oracle10g ora-00933

Nomad · Jul 31, 2011 · Viewed 11.5k times · Source

I am trying to update a table by joining the values with another table. Here's my query so far.

    UPDATE LOGIN  SET LOGIN.DISABLED_IND = 'N', LOGIN.DREASON = 'Test'
        FROM  CONTACT
        WHERE CONTACT.CONTACT_ID = LOGIN.CONTACT_ID 
        AND CONTACT.RID ='abc'

When i run this, i get this

[Error Code: 933, SQL State: 42000] ORA-00933: SQL command not properly ended

Thanks

Answer

try

 UPDATE LOGIN L SET L.DISABLED_IND = 'N', L.DREASON = 'Test'
 WHERE L.CONTACT_ID 
 IN ( SELECT C.CONTACT_ID FROM CONTACT C WHERE C.CONTACT_ID = L.CONTACT_ID AND 
 C.RID='abc');

Another more complicated option see http://geekswithblogs.net/WillSmith/archive/2008/06/18/oracle-update-with-join-again.aspx

SQL Update a table based on join with anther table

Answer

Related questions