SQL how to remove duplicate records

Question

How can I clean up a table by removing the duplicate records?

+----------+--------+------------+
| clientID | status | Insertdate |
+----------+--------+------------+
|        1 | new    |   20191206 |
|        1 | new    |   20191206 |
|        2 | old    |   20191206 |
|        2 | old    |   20191206 |
|        3 | new    |   20191205 |
|        3 | new    |   20191205 |
+----------+--------+------------+

I don't have any identity field.

Does this answer your question? [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — raven, Dec 06 '19 at 09:17
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Dec 06 '19 at 09:18

score 1 · Answer 1 · edited Dec 06 '19 at 20:18

1

Please find the below query. You can use Row Number.

;WITH cte as (
  select clientid
    , status, Insertdate
    , ROW_NUMBER() over (partition by clientid, status, Insertdate order by clientid) RowNumber 
  from Yourtable
) 
delete from cte where RowNumber > 1

edited Dec 06 '19 at 20:18

Dale K

25,246
15
42
71

answered Dec 06 '19 at 09:26

Its_Ady

308
2
10

ok thanks! with your query i'm able to select only firsts records. How can i delete the others ? – SmartDev Dec 06 '19 at 09:42
perform deletion with same query and change the end where to a.RowNumber > 1 use delete query. – Its_Ady Dec 06 '19 at 09:45
delete from (select clientid, status, Insertdate ,ROW_NUMBER() over (partition by clientid,status, Insertdate order by clientid ) RowNumber from Yourtable) a where a.RowNumber >1 doesn't work ... – SmartDev Dec 06 '19 at 09:55
@developerEmiliano i have edited the query .Please see if it works. Thanks. – Its_Ady Dec 06 '19 at 11:27
@developerEmiliano If it solved your problem ,please mark it as an answer so that others can benefit from it too. – Its_Ady Dec 09 '19 at 09:26

score -1 · Answer 2 · edited Dec 06 '19 at 10:15

-1

Hope this will help if you are running MySQL database

SELECT clientID, status, Insertdate, count(*)
FROM table_name
GROUP BY clientID, status, Insertdate
having count(*) > 1

edited Dec 06 '19 at 10:15

Dale K

25,246
15
42
71

answered Dec 06 '19 at 09:24

Mahadi Matsawily

19
1
3

Not only is it not MySQL, but OP has asked how to *delete* duplicate records. – Dale K Dec 06 '19 at 10:15
$limit = $row["count(*)"] - 1; mysqli_query($link, "DELETE FROM table_name WHERE clientID='$clientID' AND status='$status' AND Insertdate='$Insertdate' LIMIT $limit"); – Mahadi Matsawily Dec 06 '19 at 11:50

SQL how to remove duplicate records

2 Answers2