CODEWITHSUNDEEP
mysqlduplicates
Jamaseb Morteza
Jamaseb Morteza

Reputation: 49

Update duplicate rows with duplicated found id

i have table like :

id     name    description
1      foo      
2      bar
3      foo

i want update description with id of duplicated row . something like:

id     name    description
1      foo     duplicate in id (3)
2      bar     
3      foo     duplicate in id (1)

How can i do this in Mysql

Upvotes: 1

Views: 119

Answers (1)

fthiella
fthiella

Reputation: 49049

This query will return all duplicated ids with a comma separated list of ids that share the same name:

select
  t1.id,
  group_concat(t2.id)
from
  tablename t1 inner join tablename t2
  on t1.id<>t2.id and t1.name=t2.name
group by
  t1.id

and this query will update the description:

update tablename inner join (
  select
    t1.id,
    group_concat(t2.id) dup
  from
    tablename t1 inner join tablename t2
    on t1.id<>t2.id and t1.name=t2.name
  group by
    t1.id
  ) s on tablename.id = s.id
set
  description = concat('duplicate id in (', s.dup, ')')

please see a working fiddle here.

Upvotes: 1

Related Questions