0

I have a MySQL table and I want to find and delete rows which two columns have replicated values.

For example:

|ID|Name|Address|Birth_Date|
---------------------------
|1 | ab |  abc  |1990-05-24|
|2 | cd |  def  |1980-06-30|
|3 | ab |  xyz  |1990-05-24|

Here are two rows with ID 1 and 3 that refer to the same person. These rows share same Name and same Birth_Date values and may differ from one or more attributes, then they are not duplicates.

How can I find and delete these rows preserve only one of these (the first occurrence or the second occurrence)?

I want that the resulting table is the same table with only one occurrence of those rows preserved:

|ID|Name|Address|Birth_Date|
---------------------------
|1 | ab |  abc  |1990-05-24|
|2 | cd |  def  |1980-06-30|
jumpy
  • 73
  • 7
  • id 0 ? very uncommon to see id 0 in SQL? Is this deletion a one time deal? – Raymond Nijland Sep 24 '19 at 13:33
  • They are not duplicate rows, but they differ from one or more attributes – jumpy Sep 24 '19 at 13:38
  • I don't think it is a duplicate as here the last one should be kept (not required in the previous question) : First part is to get the old id for duplicates: `SELECT o.id FROM (SELECT id, name, Birth_Date FROM table) o INNER JOIN (SELECT id, name, Birth_Date FROM table) n WHERE o.name = n.name AND o.Birth_Date = n.Birth_Date and o.id – Lutz Sep 24 '19 at 13:56
  • Also test FIRST if the first query (`SELECT o.id FROM (SELECT id, name, Birth_Date FROM table) o INNER JOIN (SELECT id, name, Birth_Date FROM table) n WHERE o.name = n.name AND o.Birth_Date = n.Birth_Date and o.id – Lutz Sep 24 '19 at 14:00

1 Answers1

-1

try like below

delete  from table_name where (id,name) in (

select id,name from (
select max(id) as id, name,Birth_Date,count(*) as cnt
from table_name group by name,Birth_Date
) a where cnt>1

)

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63