Delete rows from multiple tables as one query

Question

I am running following delete statements i want to know if it is possible at all to combine them somehow:

DELETE from schools where visibility like 'revoked'

DELETE FROM bookmark_schools
WHERE school_id NOT IN (SELECT s.id FROM school s)

DELETE FROM school_addresses 
WHERE school_id NOT IN (SELECT s.id FROM school s)

DELETE FROM school_highlights 
WHERE school_id NOT IN (SELECT s.id FROM school s)

DELETE FROM school_images 
WHERE school_id NOT IN (SELECT s.id FROM school s)

...

And so on i have like 15 queries like this. Is it possible to run them together rather than running them individually?

score 0 · Answer 1 · answered Aug 03 '15 at 04:19

0

ya it is possible,but one by one.but the question is whether you want to run it in the server or by some scripting language php.

answered Aug 03 '15 at 04:19

Ujjwal Kumar Gupta

2,308
1
21
32

I want to run it on server. I know it is possible to run it one by one. I am asking if it is possible to combine them all. – NoviceMe Aug 03 '15 at 04:21
logically it is not possible,because u are doing operation on different table based on different condition – Ujjwal Kumar Gupta Aug 03 '15 at 04:25

score 0 · Answer 2 · answered Aug 03 '15 at 05:02

You can join the query as below :

DELETE FROM bookmark_schools
FROM            bookmark_schools CROSS JOIN
                         school_addresses CROSS JOIN
                         school_highlights CROSS JOIN
                         school_images CROSS JOIN
                         schools CROSS JOIN
                         school
WHERE        (NOT (bookmark_schools.school_id IN
                             (SELECT        id
                               FROM            school AS s))) AND (NOT (school_addresses.school_id IN
                             (SELECT        id
                               FROM            school AS s))) AND (NOT (school_highlights.school_id IN
                             (SELECT        id
                               FROM            school AS s))) AND (NOT (school_images.school_id IN
                             (SELECT        id
                               FROM            school AS s))) AND (schools.visibility LIKE N'revoked')

score 0 · Answer 3 · answered Aug 03 '15 at 05:08

You can achieve what you want but with some conditions as per below-

step1: tables engine should be innodb as not work in myisam.

Step2: Make foreign key references means all child tables like bookmark_schools, school_Addresses etc. have a reference of master table schools with on delete cascade on.

Step3: Now you just need to delete rows from master table i.e. schools, and foreign key check will remove from all child tables.

Further you can take help from here.

score 0 · Answer 4 · edited May 23 '17 at 12:01

0

To delete rows from multiple tables using a single query you will have to create an INNER JOIN on two tables. Refer this question

edited May 23 '17 at 12:01

Community

1
1

answered Aug 03 '15 at 07:47

Parag Jadhav

1,853
2
24
41

score 0 · Accepted Answer · answered Aug 03 '15 at 13:55

You can combine them into a single statement as others have indicated, but note that this will still be a single threaded operation.

If your goal is to improve performance / parallelize this operation, you may want to write a script which creates the 15 different client threads to run the statements concurrently.

Delete rows from multiple tables as one query

5 Answers5