MySql: delete table rows based on duplicate column values?

Question

I have a table with a year column, and this column should not have duplicate values. So, in the end, I came across a table with only one for 2007.

So, how can I delete those rows that have a duplicate year value?

thanks

Answer 1

I think you could just try adding UNIQUE INDEX using IGNORE:

ALTER IGNORE TABLE `table` ADD UNIQUE INDEX `name` (`column`); 

MySQL should answer something like:

 Query OK, 4524 rows affected (1.09 sec) Records: 4524 Duplicates: 9342 Warnings: 0 

Of course, you will leave this until MySQL to decide which rows to discard.

EDIT:

this works for as many columns as possible:

 ALTER IGNORE TABLE `table` ADD UNIQUE INDEX `name` (`col1`, `col2`, `col3`); 

check MySQL documentation for CREATE INDEX . The usual magic (at least the one I came across once) is to forget that NULL = NULL not true (but NULL ), so {42, NULL} and {42, NULL} are allowed for the UNIQUE index on two columns.

Answer 2

The accepted answer works fine, but IGNORE id keywords have depreciated now ( Source ), it will not work after MySQL 5.6 (maybe).

Although the alter table option is very simple and straightforward, the only option now is to create a new table with this query:

 CREATE TABLE <table_name> AS SELECT * FROM <your_table> GROUP BY col1,col2,col3; 

After that, you can delete <your_table> and rename <table_name> to your table.

Here you can change the list of columns in the Group By section to suit your needs (from all columns to one column or several columns that have duplicate values ​​together).

Here I also want to mention the point:

• a unique index does not change the row if any columns (from the index, for example, 3 columns) are null as a value. Example: null,1,"asdsa" can be stored twice
• if you have a single column in a unique index, then several rows with null values ​​(for this column) will remain in the table
• The advantage with create table is that it will work with null values.

Answer 3

"GROUP BY" does not work if any selection columns do not depend on the "by group" columns. See 'ONLY_FULL_GROUP_BY' sql_mode. You can change sql_mode, but I could not do it with knex (my ORM). I ended up filtering sql returned results in code.