Please change to Delete SQL without Join

kaz · December 10th, 2003, 09:48 PM

Please let me know what the SQL statement will be if I don't use JOIN keyword. I created it, but it doesn't work well.

Thanks,

DELETE Location.*
FROM Location INNER JOIN Project ON Location.LocationID = Project.LocationID
WHERE (((Project.ProjectID)=2));

owain · December 11th, 2003, 05:06 AM

You can only name one table in the FROM clause of a DELETE SQL statement. One way to get your query to work is with a sub-query.

Code:

DELETE FROM Location
      WHERE LocationID IN (SELECT LocationID
                             FROM Project
                            WHERE ProjectID = 2)

I am sure there are plenty of other ways (probably even faster ways) or doing what you want, however this query should work as you need.

Regards
Owain Williams

Jeff Mason · December 11th, 2003, 07:57 AM

SQL Server defines an extension to the DELETE syntax which allows you to specify a JOIN which defines the rows to be deleted:

Code:

DELETE Location
FROM Location INNER JOIN Project
    ON Location.LocationID = Project.LocationID
WHERE Project.ProjectID=2;

will work, but this is non-standard, and so it probably isn't available on any other RDBMS. Owain's method of using a sub-query is best (and fastest), IMO.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

kaz · December 12th, 2003, 03:16 AM

Thank you, Owain and Jeff. That helps a lot.

snowhu · December 12th, 2003, 03:29 AM

DELETE L
FROM Location L JOIN Project P ON L.LocationID = P.LocationID
AND P.ProjectID=2