CODEWITHSUNDEEP
sqloracle11g
Hilts
Hilts

Reputation: 3

How to write a SQL DELETE statement using a SELECT on a JOIN

Morning All, I'm attempting to run the following script however I'm receiving an "ORA-00933 SQL Command not properly ended" error Can anyone see where I have gone wrong:

delete tableA 
FROM tableA 
JOIN tableB
ON tableB.usi = tableA.usi
WHERE tableB.usc = 'ABC'
AND tableA.cfs = '01.01.2013'

Thanks for looking!

Upvotes: 0

Views: 46

Answers (2)

Guillaume S
Guillaume S

Reputation: 1490

You can try somethink like :

delete tableA
    WHERE id IN (
       SELECT a.id
       FROM tableA a
       JOIN tableB b
       ON b.usi = a.usi
       WHERE b.usc = 'ABC'
       AND a.cfs = '01.01.2013')

Upvotes: 0

user330315
user330315

Reputation:

Oracle does not support JOINs for a DELETE statement. You need to use a sub-query

delete from tableA 
where exists (select *
              from tableb
              where tableB.usi = tableA.usi
              and tableB.usc = 'ABC'
              AND tableA.cfs = '01.01.2013');

The full syntax of the DELETE statement is documented in the manual
https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8005.htm#SQLRF01505

Note that if tableA.cfs is a DATE (or TIMESTAMP) column, you should not rely on implicit data type conversion. '01.01.2013' is a string literal not a date. Oracle will try to convert that to a date but this might fail depending on the NLS settings of the SQL client. It's better to use explicit ansi date literals: where cfs = DATE '2013-01-01' or use the to_date() function: where cfs = to_date('01.01.2013', 'dd.mm.yyyy').

Additionally Oracle's DATE column includes a time. So unless all the dates in the csf column have the time 00:00:00 that condition is very likely to not match anything. It's safer to use trunc(tablea.csf) = ... to "remove" the time part of the date column (it doesn't really remove it, it simply sets it to 00:00:00)

Upvotes: 2

Related Questions