Merlin <[EMAIL PROTECTED]> 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
