SQL Update a table based on join with anther table

sql oracle oracle10g ora-00933

11,458

Solution 1

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

Solution 2

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

11,458

Author by

Nomad

Updated on June 13, 2022

Comments

Nomad almost 2 years

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

Nomad almost 13 years

Thanks, i tried it i am getting this error. [Error Code: 933, SQL State: 42000] ORA-00933: SQL command not properly ended
Nomad almost 13 years

Thanks a lot for your help and time, really appreciate it.
Nomad almost 13 years

Thanks a lot for your help and time, really appreciate it.
Nomad almost 13 years

Thanks a lot for your help and time, really appreciate it. Your answer was by far great and more detail and you raised excellent point of caring updating for large dataset. Let me try it and get back to you. Thanks again :).
Nomad almost 13 years

It worked like a charm. Thanks a lot for your time, lastly can you please suggest resources (book, articles) for mastering sql from beginner level to expert level. Thanks
Jon Heller almost 13 years

Not all of the examples in that article will work on Oracle. And even the Oracle specific version didn't work until I fixed it a few months ago. That article is a good example of why Wikipedia is not a good resource for programming.