sqloracle-databaseoracle10gora-00933

SQL Update a table based on join with anther table


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


Solution

  • If you expect to update a large fraction of the rows in LOGIN, it will likely be more efficient to use an EXISTS

    UPDATE LOGIN  l
       SET l.DISABLED_IND = 'N', 
           l.DREASON = 'Test'
     WHERE EXISTS (
        SELECT 1
          FROM CONTACT c
         WHERE c.CONTACT_ID = l.CONTACT_ID 
           AND c.RID ='abc' )
    

    If you are updating a relatively small fraction of the rows in LOGIN, Yahia's approach of using an IN would likely be more efficient

    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.RID ='abc' )