CODEWITHSUNDEEP
phpmysqlsql
LiggyRide
LiggyRide

Reputation: 71

Multi-table delete query not working

I have a 3 table database, and am attempting to delete from all of these tables with one query.

Query:

DELETE FROM cranes 
JOIN craneparts ON cranes.id = craneparts.craneID 
JOIN parts ON parts.id = craneparts.partsID 
WHERE cranes.id = '$id'`

Can anyone help with as to why this query isn't working? $id is simply a PHP integer, and for my testing purposes $id = 2

The given 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 'JOIN craneparts ON cranes.id = craneparts.craneID JOIN parts ON parts.id = crane' at line 1`]

Upvotes: 1

Views: 62

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

You need to specify the tables you want to delete from after the delete:

DELETE cranes, craneparts, parts
    FROM cranes JOIN
         craneparts
         ON cranes.id = craneparts.craneID JOIN
         parts
         ON parts.id = craneparts.partsID
     WHERE cranes.id = '$id';

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562681

Your query must be specific about which tables you want it to delete from, even if you want to delete from all tables.

DELETE cranes, craneparts, parts
FROM cranes JOIN craneparts ON cranes.id = craneparts.craneID 
JOIN parts ON parts.id = craneparts.partsID 
WHERE cranes.id = '$id'

See http://dev.mysql.com/doc/refman/5.6/en/delete.html for more documentation.

Upvotes: 2

Related Questions