Abdallah Qaraeen Abdallah Qaraeen - 9 days ago 7
SQL Question

SQL left join unable to delete rows?

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:

Answer

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