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