CODEWITHSUNDEEP
mysqlsql
Tum
Tum

Reputation: 3652

MYSQL query to Update the field if found duplicates?

Here is my problem. I got a table Meaning

ID - Meaning
1 - red car
2 - cat man
3 - red car
4 - ontime
5 - red car
....

I want to make the colum Meaning become Unique. So i want to build a query to found all the duplicates & for each of duplicate, the system should append [number] to make the cell become unique.

So after running that query, the result should be:

ID - Meaning
1 - red car
2 - cat man
3 - red car [2]
4 - ontime
5 - red car [3]
....

The table is pretty long about 100K rows. The query could be similar to this query

Update Table Meaning set meaning=concat(meaning,"1") 
where meaning in (select meaning from Meaning group by meaning having count(meaning>1)

So what is the query for solving the problem?

Seem we have to use set variable to check each row?

Upvotes: 3

Views: 108

Answers (2)

sumit
sumit

Reputation: 15464

step 1: create temporary table

CREATE TABLE TMP (id int, meaning varchar (2));

step 2: prepare query and insert into temporary table

insert into tmp 
SELECT id, 
CASE WHEN cnt =0 theN meaning ELSE concat(meaning,'[',cnt+1,']') END AS meaning 

FROM
(
SELECT t1.id, t1.meaning, (

SELECT COUNT( t.id ) 
FROM test t
where  t.meaning=t1.meaning 
and t.id<t1.id
) as cnt
FROM test t1
)TMP

step 3 

truncate table test

step 4: migrate to original

insert into test select * from tmp

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

SELECT x.*
     , CONCAT(x.meaning,CASE WHEN COUNT(*) = 1 THEN '' ELSE COUNT(*) END) meaning 
  FROM meanings 
     x JOIN meanings 
     y ON y.meaning = x.meaning 
   AND y.id <= x.id 
 GROUP 
    BY id;

Upvotes: 0

Related Questions