Delete query

[mrt003003]

Hi there,

 

Im trying to delete a record from one table where the id of that table is not used in the 2nd table. I can get it to work fine with a select but not as a delete:

 

SELECT *
  FROM Fleet f
LEFT JOIN Ships s ON (f.FleetID = s.FleetID)
WHERE s.FleetID IS NULL

 

This works fine but if i try to delete

 

DELETE *
  FROM Fleet f
LEFT JOIN Ships s ON (f.FleetID = s.FleetID)
WHERE s.FleetID IS NULL

 

It gives me a mysql error??

 

Any ideas??

 

Thanks

[Pikachu2000]

And what is that error, pray tell?

[awjudd]

http://dev.mysql.com/doc/refman/5.0/en/delete.html

 

Look at the part in the documentation about deleting with JOINs.

 

~juddster

[mrt003003]

The error is: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'f LEFT JOIN Ships s ON (f.FleetID = s.FleetID) WHERE s.FleetID IS NULL' at line 2

Thanks

[mrt003003]

Yay its fixed

DELETE fleet.*
  FROM Fleet
LEFT JOIN Ships ON fleet.FleetID = ships.FleetID
WHERE ships.FleetID IS NULL 

 

Thank you