CODEWITHSUNDEEP
mysql
Mahmoud Al-Qudsi
Mahmoud Al-Qudsi

Reputation: 29579

MySQL: Delete from one table where not in another without using multiple statements

I'm trying to clean up a 7.5GiB table in MySQL by executing the following command:

DELETE FROM wp_commentmeta WHERE comment_id NOT IN  (SELECT comment_id FROM wp_comments);

There is no foreign key between the two fields. Because of the size of (the second? both?) tables, attempting to execute this results in the following error:

Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

The table is huge enough that I can't feasibly raise binlog_cache_size to accommodate this request. Short of dumping the two tables to disk and diffing their contents with a parser offline, is there some way to restructure the query to more-efficiently perform what I need to do?

Some of things I could do (but I wish to choose the correct/smart course of option):

Suggestions welcome, please!

Upvotes: 0

Views: 150

Answers (1)

Husni
Husni

Reputation: 1065

Try this one:

DELETE wcm
FROM wp_commentmeta wcm
LEFT JOIN wp_comments wc ON wc.comment_id = wcm.comment_id
WHERE wc.comment_id IS NULL;

Upvotes: 1

Related Questions