CODEWITHSUNDEEP
mysqlsql-delete
Jason
Jason

Reputation: 61

Deleting rows from one table if row exists in another table in MySQL

I am migrating data from an old training tracking system and I am using MySQL to format the data for the new training tracking system.

I have one table with 5.5 million rows which is the master set of data from the old system. I also have a table with around 49,000 rows that had been migrated previously.

What I am trying to do is compare the two tables and remove from the master data file the records that have been previously migrated (I don't want to create duplicate records in the new system.)

For the comparison I need to compare 3 fields (employee_id, course_code, and completion_date.) I am using the following SQL statement, but it just sits and spins. I don't know if it is working and just taking a long time or if it is really not working.

DELETE master_data.*
FROM master_data
INNER JOIN alreadyMigrated
ON master_data.employee_id = alreadyMigrated.employee_id 
AND master_data.course_code = alreadyMigrated.course_code
AND master_data.completion_date = alreadyMigrated.completion_date;

I also don't know if indexes would help. Any help would be appreciated. Thanks.

Upvotes: 5

Views: 12090

Answers (1)

John Woo
John Woo

Reputation: 263693

you just need to specify the table name, not the columns.

DELETE master_data
       FROM master_data
       INNER JOIN alreadyMigrated
          ON master_data.employee_id = alreadyMigrated.employee_id AND 
             master_data.course_code = alreadyMigrated.course_code AND 
             master_data.completion_date = alreadyMigrated.completion_date;

Upvotes: 3

Related Questions