How to delete the rows which are repeated in Mysql

Question

I have a Mysql table like this

supplierranks_new

Id
supplierid
userid
rank

Now I have got a Garbage data where a user whose userid=1837 has ranked one supplier too many times so there is multiple row of the same ranking , I want to remove all those and keep one

for example

1 267 1837 23
2 267 1837 23
3 267 1837 23
4 267 1837 23
5 267 1837 23
6 268 1837 1
7 268 1837 1
8 268 1837 1
9 268 1837 1
10 268 1837 1

Now I want to change it to this

 1 267 1837 23
 6 268 1837 1

Any Idea how I can write the query

Thanks

http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table — Steve, Nov 23 '15 at 13:07
See Steve's link above. If that's what you want and solves this question, I can mark as a duplicate. — Funk Forty Niner, Nov 23 '15 at 13:09
I'd recommend that you add constraints to make it impossible for a user to rate a supplier incorrectly. Let the database enforce it. — duffymo, Nov 23 '15 at 13:09
@duffymo actually its already there it happened I was fiddeling with a file on the live server and I am using Laravel something went wrong and on that point of time when the user ranked it saved multiple rows in the DB — Vikram Anand Bhushan, Nov 23 '15 at 13:10
@Steve One should be cautious when using [`IGNORE`](https://dev.mysql.com/doc/refman/5.7/en/alter-table.html) clause: `As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error.` — Giorgos Betsos, Nov 23 '15 at 13:12
check this link http://www.besttechtools.com/articles/article/sql-query-to-delete-duplicate-rows — Parth Chavda, Nov 23 '15 at 13:13
Possible duplicate of [Delete all Duplicate Rows except for One in MySQL?](http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql) — viral, Nov 23 '15 at 13:18

score 2 · Accepted Answer · edited Nov 23 '15 at 13:28

You can use the following query:

DELETE FROM supplierranks_new
WHERE `Id` NOT IN (
  SELECT `Id`
  FROM (SELECT MIN(`Id`) AS `Id`
        FROM supplierranks_new
        GROUP BY `supplierid`, `userid`, `rank`) AS t )

This will delete any duplicate row not having the minimum Id value within the supplierid, userid, rank slice.

Note the usage of a subquery inside the NOT IN operator in order to avoid:

You can't specify target table 'supplierranks_new' for update in FROM clause

error.

Demo here

Abhishek Sharma · Answer 2 · 2015-11-23T13:30:43.430

0

Add Unique Index on your table,This will drop all the duplicate rows.

ALTER IGNORE TABLE supplierranks_new
ADD UNIQUE INDEX (supplierid,userid);

or this :-

ALTER TABLE supplierranks_new ADD UNIQUE supplierid ( supplierid )

edited Nov 23 '15 at 13:30

answered Nov 23 '15 at 13:06

Abhishek Sharma

6,689
1
14
20

1

you should have asked first (like I did). They want to delete permanently. – Funk Forty Niner Nov 23 '15 at 13:08
@Rahautos I want to remove those line from the table – Vikram Anand Bhushan Nov 23 '15 at 13:08
As per your edit, read the comments from above: *"I'd recommend that you add constraints to make it impossible for a user to rate a supplier incorrectly. Let the database enforce it. – duffymo 14 mins ago"* --- *"@duffymo actually its already there it happened I was fiddeling with a file on the live server and I am using Laravel something went wrong and on that point of time when the user ranked it saved multiple rows in the DB – Vikram 13 mins ago"* – Funk Forty Niner Nov 23 '15 at 13:25
I thought this latest edit would work. Maybe my settings are wrong, but MySQL doesn't seem to like this - regardless of whether the PK has been removed. – Strawberry Nov 23 '15 at 13:36

score 0 · Answer 3 · answered Nov 23 '15 at 13:32

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,supplierid INT NOT NULL
,userid INT NOT NULL
,rank INT NOT NULL
);

INSERT INTO my_table VALUES
(1,267,1837,23),
(2 ,267 ,1837 ,23),
(3 ,267 ,1837 ,23),
(4 ,267 ,1837 ,23),
(5 ,267 ,1837 ,23),
(6 ,268 ,1837 ,1),
(7 ,268 ,1837 ,1),
(8 ,268 ,1837 ,1),
(9 ,268 ,1837 ,1),
(10 ,268 ,1837, 1);

CREATE TABLE my_new_table 
(supplierid INT NOT NULL
,userid INT NOT NULL
,rank INT NOT NULL
,PRIMARY KEY(supplierid,userid)
) AS 
SELECT supplierid
     , userid
     , MIN(rank) rank 
  FROM my_table 
 GROUP 
    BY supplierid
     , userid;

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

SELECT * FROM my_new_table;
+------------+--------+------+
| supplierid | userid | rank |
+------------+--------+------+
|        267 |   1837 |   23 |
|        268 |   1837 |    1 |
+------------+--------+------+

score 0 · Answer 4 · answered Nov 23 '15 at 13:51

Although, I would go for adding UNIQUE to (supplierid, userid), which should take care of duplicate rows.

Else, instead of subqueries, I would rather go for:

DELETE sn1 
FROM supplierranks_new sn1, supplierranks_new sn2
  WHERE sn1.id > sn2.id
    AND sn1.supplierid = sn2.supplierid
    AND sn1.userid = sn2.userid
;

In the JOIN of same table, delete all the records with matching conditions and with id of first greater than id of second. This yields records with least id. If you want records with latest id, simply inverse the greater than condition:

WHERE sn1.id < sn2.id

HTH

How to delete the rows which are repeated in Mysql

4 Answers4