MySql: remove table rows depending on column duplicate values?

Question

I have a table with year column and this column shouldn't have duplicate values. So I end up with a table with only one 2007 year record for example.

So how could I delete those rows that have duplicate year value?

Thanks

Answer 1

I think you could simply try adding a UNIQUE INDEX using IGNORE:

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

MySQL should respond with something like:

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

Of course, you'll leave it up to MySQL to decide which rows to drop.

EDIT:

this works for as many columns as you like:

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

check MySQL's documentation on CREATE INDEX. A common gotcha (at least one I ran into once) is to forget that NULL = NULL isn't true (but NULL), hence {42, NULL} and {42, NULL} are allowed for a UNIQUE index on two columns.

Answer 2

Accepted answer works perfectly, but IGNORE keywords id depreciated now(Source), it will not work after MySQL 5.6(may be).

Although alter table option is very easy and direct BUT, Now only option is to create new table by a query like this:

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 column list in Group By clause according to your need(from all columns to one column, or few columns which have duplicate values together).

Here I also want to mention a point:

1. unique index does not make change in row if any columns(from index, like here 3 columns) have null as value. Ex: null,1,"asdsa" can be stored twice
2. same way if you have single column in unique index then multiple rows with null values(for that column) will remains in table
3. The plus point with create table is, it will work with null values also.