Merlin <ngroups@stripped> wrote on 10/09/2005 01:31:17 PM:
> Hi there,
> I am wondering if there is a possiblity to delete rows in more than one
> table with one query.
> At the moment I am doing this:
> I do have at the moment 3 querys!:
> # get table1_id
> SELECT table1_id
> from ...
> WHERE ID = ...
> # delete dependent entry
> FROM $DB.$T5
> ID = '$data[table1_id]'
> LIMIT 1
> # delete main entry
> FROM $DB.$T4
> ID = '$data[id]'
> LIMIT 1
> Is there a better solution as this?
> Thanx for any hint,
Have you considered RTFM? http://dev.mysql.com/doc/mysql/en/delete.html
There are two forms that accept multiple tables as the targets of the
deletion and as the determinates for the deletion. I would still run two
separate queries, just to make sure there was no
parent-child-deletion-sequence race condition:
DELETE FROM child
USING parent p
INNER JOIN child c
ON c.parent_id = p.id
WHERE p.id = ...;
DELETE FROM parent
WHERE id = ...;
The other suggestion about cascading deletes would only work if both
tables are InnoDB and you have defined the appropriate foreign key (as
Unimin Corporation - Spruce Pine