Removing Data from One MySQL Table Carefully

I had to purge some data from a MySQL table, but wanted to do this carefully. This following procedure worked for my table:

1) SHOW CREATE TABLE

SHOW CREATE TABLE yourschema.yourtable

2) CREATE TABLE

Using the output of 'SHOW CREATE TABLE' copy the structure of the table in question into a backup copy of the table using CREATE TABLE, i.e:

CREATE TABLE yourschema.yourtable_BKP ( ...

3) INSERT INTO

Next we insert data from the original table into the backup table:

INSERT INTO yourschema.yourtable_BKP SELECT * FROM yourschema.yourtable

4) Get the Data to be Removed

This next step will depend on your circumstances. I was provided with a CSV of the data to be removed from the original table, and I simply right-clicked the schema/database in MySQL WorkBench, and used the 'Table Data Import Wizard' to import the CSV of data.

5) DELETE FROM

Again, this will depend on your circumstances, but I found it easiest to use an SQL query to create a load of DELETE FROM statements by analyzing the imported CSV, and the original table data. Something like this:

SELECT CONCAT('DELETE FROM yourschema.yourtable WHERE id = ',f.id,';') AS 'deletes'
FROM yourschema.yourtable AS f
JOIN yourschema.your_imported_data AS b ON f.device_mgmt_mac = b.MAC;

This creates a load of DELETE FROM statements, and you can run these to purge the data requiring purging.

6) Tidy Up

The final thing you'll want to do is perform tidy up of imported CSV:

DROP TABLE yourschema.your_imported_data;

And later on (once you're sure nothing was purged that shouldn't have been), tidy up the backup table:

DROP TABLE yourschema.yourtable_BKP;

THE END