CODEWITHSUNDEEP
phpmysql
Ben Greene
Ben Greene

Reputation: 147

DELETE FROM not working for multiple tables - clueless with mysql

Basically, I have three tables

workplaces with a field id (the one of which i want to delete the associated data), and a participantlist_id that identifies the participant list.

participantlist - just list_id and name

participantlist_links - with list_id and participant_id

the query that i'm trying to use is this:

DELETE FROM workplaces,
participantlist,
participantlist_links 
WHERE workplaces.id =  '8' AND 
      workplaces.participantlist_id = participantlist_links.list_id AND  
      workplaces.participantlist_id = participantlist.list_id

but i get:

MySQL said:

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 'WHERE workplaces.id='8' AND workplaces.participantlist_id = participant' at line 3

any ideas?

SOLUTION:

    DELETE t1, t2, t3
    FROM workplaces AS t1
    LEFT JOIN participantlist AS t2 ON t1.participantlist_id = t2.list_id
    LEFT JOIN participantlist_links AS t3 ON t2.list_id = t3.list_id
    WHERE t1.id = '8'

Upvotes: 1

Views: 397

Answers (4)

BasTaller
BasTaller

Reputation: 810

You can use JOIN to delete from multiple tables like this:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

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

Upvotes: 1

eggyal
eggyal

Reputation: 125945

As mentioned in the manual, the multiple-table forms of DELETE have different syntax to that which you're using. You want:

DELETE workplaces, participantlist, participant_links
FROM   workplaces
  JOIN participantlist
    ON workplaces.participantlist_id = participantlist.list_id
  JOIN participantlist_links
    ON workplaces.participantlist_id = participantlist_links.list_id
WHERE  workplaces.id = '8'

Upvotes: 1

Kasia Gogolek
Kasia Gogolek

Reputation: 3414

Try

DELETE workplaces, participantlist, participantlist_links FROM workplaces,
participantlist,
participantlist_links 
WHERE workplaces.id =  '8' AND 
      workplaces.participantlist_id = participantlist_links.list_id AND  
      workplaces.participantlist_id = participantlist.list_id

Upvotes: 1

Rick Kuipers
Rick Kuipers

Reputation: 6607

Have you considered putting ON DELETE CASCADE on the foreign keys? This way when you delete the row from workspace, the other rows will be automatically deleted as well.

See this link for more information: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Otherwise you'd have to use seperate DELETE FROM queries to delete them all.

It's smart to use a transaction while executing these deletes. So if anything goes wrong you can rollback your changes:

BEGIN TRANSACTION;
DELETE FROM ......;
DELETE FROM ......;
COMMIT;

Upvotes: 2

Related Questions