DELETE TECHNIQUE #1
If you have a massive delete of keys, you may want to resort to the opposite of doing a massive DELETE: Create the table with the remaining values.
Let's say you loaded a table from the CSV file called
KeysToDelete with the field
Here is how to perform the massive
DELETE without using
CREATE TABLE mynewtable FROM mytable; INSERT INTO mynewtable SELECT A.* FROM mytable A LEFT JOIN KeysToDelete B USING (mykey) WHERE B.mykey IS NULL; ALTER TABLE mytable RENAME myoldtable; ALTER TABLE mynewtable RENAME mytable;
DELETE TECHNIQUE #2
If you want to use
DELETE and the table is not that big, you could do a
DELETE JOIN as follows:
DELETE A.* FROM mytable A INNER JOIN KeysToDelete B USING (mykey);
Here are my earlier posts where I discuss these techniques:
Feb 15, 2012: http://dba.stackexchange.com/questions/13134/how-to-implement-soft-deletes/13137#13137How to implement soft deletes?
Mar 05, 2012: http://dba.stackexchange.com/questions/14402/tombstone-table-vs-deleted-flag-in-database-syncronization-soft-delete-scenari/14419#14419Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios
Nov 07, 2012: http://dba.stackexchange.com/questions/28272/can-i-do-anything-about-a-very-long-running-kill-command/28338#28338Can I do anything about a very long running KILL command?
Aug 07, 2013: http://dba.stackexchange.com/questions/47732/how-to-delete-duplicate-records/47736#47736How to delete duplicate records