469,592 Members | 2,032 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,592 developers. It's quick & easy.

DELETE FROM multiple tables

code green
1,726 Expert 1GB
I have a parent table `products` that contains all company products.
The product details are divided amongst 4-5 product details child tables
such as `paper`, `pens`, `books`, `sets`, `pens_paper`.
The 'product_ids` may be in one or more of these child tables but always in `products`.
Is a single DELETE query possible that will delete all occurences of a `product_id` in the tables where the `product_id` occurs.
Mar 20 '07 #1
3 9695
Motoma
3,237 Expert 2GB
I have a parent table `products` that contains all company products.
The product details are divided amongst 4-5 product details child tables
such as `paper`, `pens`, `books`, `sets`, `pens_paper`.
The 'product_ids` may be in one or more of these child tables but always in `products`.
Is a single DELETE query possible that will delete all occurences of a `product_id` in the tables where the `product_id` occurs.
One way to do this would be to use foreign keys between the tables and have cascading deletes in place.
Mar 20 '07 #2
code green
1,726 Expert 1GB
Thanks for the reply but I asked for a QUERY that performs a multiple delete based on the conditions.
Mar 28 '07 #3
code green
1,726 Expert 1GB
I seem to have solved it without any help.
Will post my solution
Expand|Select|Wrap|Line Numbers
  1. --delete id 100 wherever it appears across the database--
  2. DELETE FROM `table1`,`table2`,`table3` USING `table1` 
  3. LEFT JOIN `table2` ON `table2`.`id` = 100  AND any_other_condition_for_table2
  4. LEFT JOIN `table3` ON `table3`.`id` = 100 AND any_other_condition_for_table3
  5. WHERE `table1`.`id` = 100
The left join solves the problem of the possibility of the id not appearing in a certain table
Mar 28 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Ray | last post: by
5 posts views Thread by Mike | last post: by
2 posts views Thread by Eric Haskins | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.