List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 10 2005 1:59pm
Subject:Re: deleting rows in 2 tables
View as plain text  
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
>    DELETE
>        FROM $DB.$T5
>    WHERE
>        ID = '$data[table1_id]'
>    LIMIT 1
> 
>    # delete main entry
>    DELETE
>        FROM $DB.$T4
>    WHERE
>        ID = '$data[id]'
>    LIMIT 1
> 
> Is there a better solution as this?
> 
> Thanx for any hint,
> 
> Merlin
> 

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 
described).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
deleting rows in 2 tablesMerlin9 Oct
  • Re: deleting rows in 2 tablesRhino9 Oct
    • Re: deleting rows in 2 tablesJason Dimberg9 Oct
  • Re: deleting rows in 2 tablesSGreen10 Oct