Deleting equal rows in mySQL 5.7.9?

Question

I have this table in mysql called ts1

+----------+-------------+---------------+
| position | email       | date_of_birth |
+----------+-------------+---------------+
|        3 | NULL        | 1987-09-03    |
|        1 | NULL        | 1982-03-26    |
|        2 | Sam@gmail   | 1976-10-03    |
|        2 | Sam@gmail   | 1976-10-03    |
+----------+-------------+---------------+

I want to drop the equal rows using ALTER IGNORE.

I have tried

ALTER IGNORE TABLE ts1 ADD UNIQUE INDEX inx (position, email, date_of_birth);

and

ALTER IGNORE TABLE ts1 ADD UNIQUE(position, email, date_of_birth);

In both cases I get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE ts1 ADD UNIQUE(position, email, date_of_birth)' at line 1

I'm using mySQL 5.7.9. Any suggestions?

perhaps a PK needs to be added, so when you manually delete them (or thru some keep the min() strategy), you have row or rows to target. Then when it is all cleaned up, get that unique composite in there — Drew, Oct 30 '15 at 16:52
yes, much easier, as Strawberry said, [see pastie](http://pastie.org/10518490) — Drew, Oct 30 '15 at 16:57
Just an idea - create a query with `count` aggregation function, with `group by everything`. Than wrap it with another query that deletes all rows which have count > 1 — SomethingSomething, Oct 30 '15 at 17:03
maybe he wants to keep a single row that had a dupe, not delete all of them — Drew, Oct 30 '15 at 17:04
Another idea - add the records one by one to a temporary table. Before each insertion, check whether there's already such a record in the temporary table - if there is - delete it from the original table — SomethingSomething, Oct 30 '15 at 17:04

score 0 · Accepted Answer · answered Oct 30 '15 at 17:34

To do it inline against the table, given just the columns you show consider the below. To do it in a new table as suggested by Strawberry, see my pastie link under comments.

create table thing
(   position int not null,
    email varchar(100) null,
    dob date not null
);
insert thing(position,email,dob) values
(3,null,'1987-09-03'),(1,null,'1982-03-26'),
(2,'SamIAm@gmail.com','1976-10-03'),(2,'SamIAm@gmail.com','1976-10-03');
select * from thing;
+----------+------------------+------------+
| position | email            | dob        |
+----------+------------------+------------+
|        3 | NULL             | 1987-09-03 |
|        1 | NULL             | 1982-03-26 |
|        2 | SamIAm@gmail.com | 1976-10-03 |
|        2 | SamIAm@gmail.com | 1976-10-03 |
+----------+------------------+------------+

alter table thing add id int auto_increment primary key;

Delete with a join pattern, deleting subsequent dupes (that have a larger id number)

delete thing
from thing
join
( select position,email,dob,min(id) as theMin,count(*) as theCount 
  from thing 
  group by position,email,dob 
  having theCount>1
) xxx -- alias
on thing.position=xxx.position and thing.email=xxx.email and thing.dob=xxx.dob and thing.id>xxx.theMin
-- 1 row affected

select * from thing;

+----------+------------------+------------+----+
| position | email            | dob        | id |
+----------+------------------+------------+----+
|        3 | NULL             | 1987-09-03 |  1 |
|        1 | NULL             | 1982-03-26 |  2 |
|        2 | SamIAm@gmail.com | 1976-10-03 |  3 |
+----------+------------------+------------+----+

Add the unique index

CREATE UNIQUE INDEX `thing_my_composite` ON thing (position,email,dob); -- forbid dupes hereafter

View current table schema

show create table thing;

CREATE TABLE `thing` (
  `position` int(11) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `dob` date NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `thing_my_composite` (`position`,`email`,`dob`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Deleting equal rows in mySQL 5.7.9?

1 Answers1