SQL DELETE syntax

[DrDoc]

I'm running a DELETE query and want to delete rows from two tables. The tables are inner joined, but I am also left joining the first table again, but do not want to delete anything which satisfies that join.

DELETE st1, ot 
FROM SomeTable st1 
  INNER JOIN OtherTable ot ON st1.cID = ot.cID 
    AND st1.sID = ot.sID 
  LEFT JOIN SomeTable st2 ON ot.pcID = st2.cID 
    AND ot.sID = st2.sID 
WHERE ot.pcID!= '0' 
  AND st2.Name IS NULL

It complains about my use of table aliases. Now, I know that in this case I can probably just say "

DELETE SomeTable, OtherTable

" due to "

AND st2.Name IS NULL

" (which is never null unless the LEFT JOIN is unsuccessful).

But, I know I will also need to do a second DELETE query where the table is inner joined to itself, deleting only what matches in the first use, but not anything which satisfies the INNER JOIN itself. In that case, table aliases would be a must.

How can I accomplish this?

[edited by: DrDoc at 4:17 pm (utc) on Oct. 3, 2006]

[coopster]

I've looked at this a few times, DrDoc. All seems fine to me. What version of which database are you running? I tested it in a MySQL 5.0 database without issue.

[syber]

To avoid problems like this, you should always use the ANSI SQL syntax for DELETE statements (use subqueries instead of joins).

DELETE  
FROM SomeTable  
WHERE EXISTS 
 (SELECT *  
  FROM OtherTable ot  
  WHERE SomeTable.cID = ot.cID 
   AND SomeTable.sID = ot.sID  
  LEFT JOIN SomeTable st2  
  ON ot.pcID = st2.cID   
  AND ot.sID = st2.sID 
  WHERE ot.pcID!= '0'  
  AND st2.Name IS NULL) 

To test which rows are going to be deleted, replace DELETE with SELECT *

[coopster]

you should always use the ANSI SQL syntax for DELETE statements (use subqueries instead of joins)

That depends on which ANSI standard. If I remember correctly, core SQL-99 states that you shouldn't use a search condition that specifies an underlying table of the target table. Times have indeed changed though and subquery sytnax would be ideal here, except I'm guessing DrDoc is using a MySQL database and currently you cannot delete from a table and select from the same table in a subquery in MySQL. Hopefully that changes in the near future.

[aspdaddy]

Thats like saying you should only use the features in Notepad for writing letters :)

Id only use ANSI if I need the portability, otherwise you are much better off using the databases own documentation.

[syber]

My point was that with the modification statements UPDATE and DELETE, it is best to use ANSI SQL to avoid incorrect results that database extensions sometimes cause.