...

View Full Version : Delete info from 4 tables at once but with different name for key



maghiel
03-23-2007, 09:55 AM
Hello,

I have four tables, let's say



tbl1 tbl2 tbl3 user
------- ------- ------- ----------
id id id id
userid userid userid info
info info info


Now how to delete everything of one user in a single query?

SSJ
03-23-2007, 10:57 AM
Here is the only way to do this:


Delete from tbl1 where userid=11;Delete from tbl2 where userid=11;Delete from tbl3 where userid=11;Delete from tbl4 where userid=11;

maghiel
03-23-2007, 11:18 AM
too bad.., i love to do everything in a single query ;)

maghiel
03-23-2007, 01:09 PM
Here is the only way to do this:


Delete from tbl1 where userid=11;Delete from tbl2 where userid=11;Delete from tbl3 where userid=11;Delete from tbl4 where userid=11;

wrong!
see: http://dev.mysql.com/doc/refman/5.0/en/delete.html

I solved it like this:


DELETE FROM `tbl1`, `tbl2`, `tbl3`
USING `tbl`, `tbl2`, `tbl3`,
WHERE
(`tbl1`.`id` = `tbl3`.`tbl1id` OR `tbl1`.`userid` = '23')
OR
(`tbl2`.`id` = `tbl3`.`tbl2id` OR `tbl2`.`userid` = '23')



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum