0

So i am trying to delete some rows in a left joined table using the following code in sql:

DELETE gw_svd_prefix_assignment
FROM gw_svd_prefix_assignment svdp
left join assyst_view av
on upper(svdp.user_name) = upper(av.usr_sc)
where upper(av.usr_sc) IS NULL
commit;

but i am getting this error:

Error starting at line : 1 in command -
DELETE gw_svd_prefix_assignment
FROM gw_svd_prefix_assignment svdp
left join assyst_view av
on upper(svdp.user_name) = upper(av.usr_sc)
where upper(av.usr_sc) IS NULL
commit
Error at Command Line : 2 Column : 1
Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Abdallah Qaraeen
  • 113
  • 10

1 Answers1

2

Oracle does not support that syntax. UPDATE and DELETE only work on one table . . . the FROM clause cannot contain multiple tables.

Instead, put the logic in the WHERE clause:

delete from gw_svd_prefix_assignment svdp
    where not exists (select 1
                      from assyst_view av
                      where upper(svdp.user_name) = upper(av.usr_sc)
                     );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • COOL! Thanks for your help! Worked perfectly fine. :) – Abdallah Qaraeen Nov 30 '16 at 15:12
  • @AbdallahQaraeen - did you try to add the `;` first, as suggested by someone in the Comments? That was obviously missing from what you posted. –  Nov 30 '16 at 15:14
  • @AbdallahQaraeen - I just tested, and Oracle **does** support deleting with JOIN; I tested a case exactly like yours, with a left join and where what is deleted are exactly the rows that wouldn't match in an inner join. I also just created a Documentation article "Updates with Join" under the `Oracle Database` tag addressing the misconception that Oracle does not support UPDATE with joins. –  Nov 30 '16 at 21:31