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.