Clever Geek Handbook

Removing rows from multiple tables as a single query

I am running the following removal instructions that I want to know if it is possible to combine them at all:

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 15 requests like this. Can you run them together instead of running them separately?

+3


source to share


5 answers


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



If your goal is to improve performance / parallelize this operation, you can write a script that creates 15 different client threads to run statements at the same time.

0


source


ya maybe, but one by one. But the question is whether you want to run it on a server or some kind of php scripting language.



0


source


You can join the request 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')
0


source


You can achieve what you want, but with some conditions given below -

step1: table engine must be innodb as it doesn't work in myisam.

Step2: Making foreign key references means that all child tables like bookmark_schools, school_Addresses, etc. have a reference to the master tables with the delete cascade enabled.

Step 3. Now you just need to delete rows from the main table, i.e. schools, and the foreign key check will be removed from all child tables.

Further you can use here .

0


source


To delete rows from multiple tables with one query, you will need to create INNER JOIN

on two tables. Refer to question

0


source






More articles:


All Articles