Mysql delete column workbench update#

On line #3, the ORDER BY id DESC clause makes the last ID's to get priority over the rest.

INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id DESC Sometimes we need to keep the last duplicated entry instead of the first one. ✔ Variation for keeping the last entry instead of the first one Of course, this process can be further modified to adapt it for different needs when deleting duplicates. RENAME TABLE jobs TO backup_jobs, tmp_jobs TO jobs INSERT IGNORE INTO tmp_jobs SELECT * FROM jobs ORDER BY id ⇒ Using this approach, 1.6M registers were converted into 6k in less than 200s.Ĭhetan, following this process, you could fast and easily remove all your duplicates and create a UNIQUE constraint by running: CREATE TABLE tmp_jobs LIKE jobs ĪLTER TABLE tmp_jobs ADD UNIQUE(site_id, title, company)

Line #4 renames tables, so that the new employee table holds all the entries without the duplicates, and a backup copy of the former data is kept on the backup_employee table.

Line #3 scans over the original employee table by id, inserting new employee entries into the new tmp_eployee table, while ignoring duplicated entries.

Line #2 adds a UNIQUE constraint to the new tmp_eployee table to avoid any further duplicates.

Line #1 creates a new tmp_eployee table with exactly the same structure as the employee table.

RENAME TABLE employee TO backup_employee, tmp_employee TO employee INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id scan over the employee table to insert employee entries In order to delete the rows with a duplicate ssn column, and keeping only the first entry found, the following process can be followed: - create a new tmp_eployee tableĪLTER TABLE tmp_employee ADD UNIQUE(ssn) Given we have a table employee, with the following columns: employee (id, first_name, last_name, start_date, ssn)

This approach relies on simple MySQL INSERT queries, creates a new constraint to avoid further duplicates, and skips the need of using an inner query to search for duplicates and a temporary table that should be kept in memory (thus fitting big data sources too). The general idea is to create a new temporary table, usually adding a unique constraint to avoid further duplicates, and to INSERT the data from your former table into the new one, while taking care of the duplicates. However, a better approach does exist to remove duplicates, that's both efficient and reliable, and that can be easily adapted to different needs. But this inner query won't perform specially well when dealing with big data sources. This limitation can be overcome by using an inner query with a temporary table (as suggested on some approaches above).

Mysql delete column workbench update#

There are also some specificities on MySQL itself, such as not being able to reference the same table on a FROM cause when performing a table UPDATE (it'll raise MySQL error #1093). The approach should be different depending on, for example, the size of the data, the duplicated entry that should be kept (generally the first or the last one), whether there are indexes to be kept, or whether we want to perform any additional action on the duplicated data. But this common issue usually comes with specific needs. Change 'ID' to your unique column's nameĭeleting duplicates on MySQL tables is a common issue, that's genarally the result of a missing constraint to avoid those duplicates before hand. Since you have a column which has unique IDs (e.g., auto_increment columns), you can use it to remove the duplicates: DELETE `a` If you don't want to alter the column properties, then you can use the query below.