How to delete rows from a table in database X, where the ID exists in Database Y

Question

I've got 2 mysql 5.7 databases hosted on the same server (we're migrating from 1 structure to another)

I want to delete all the rows from database1.table_x where the there is a corresponding row in database2.table_y

The column which contains the data to match on is called code

I'm able to do a SELECT which returns everything that is expected - this is effectively the set of data I want to delete.

An example select would be:

SELECT * 
FROM `database1`.`table_x` 
WHERE `code` NOT IN (SELECT `code` 
                     FROM `database2`.`table_y`);

This works and it returns 5 rows within 138ms.

--

However, If I change the SELECT to a DELETE e.g.

DELETE
FROM `database1`.`table_x` 
WHERE `code` NOT IN (SELECT `code` 
                     FROM `database2`.`table_y`);

The query seems to hang - there are no errors returned, so I have to manually cancel the query after about 3 minutes.

--

Could anyone advise the most efficient/fastest way to achieve this?

Answer 1

try like below it will work

DELETE FROM table_a WHERE `code` NOT IN (      

        select * from 
         (
          SELECT `code` FROM `second_database`.`table_b`        
         ) as t

     );