sql - MySQL update query for duplicate entries -

sql - MySQL update query for duplicate entries -

i want sql updation such :

assume have next table in mysql database

titles

id title 1 hello 2 hello 3 hi 4 sometext 5 hi 6 hello

i want table updated such there unique titles.

titles

id title 1 hello 2 hello1 3 hi 4 sometext 5 hi1 6 hello2

actually working on migration script in unique titles constraint not used initially.

update titles t inner bring together ( select id, title, case when title=@curtitle @currank := @currank + 1 else @currank:=0 end, @curtitle:=title, @currank rank titles t, (select @currank := 0,@curtitle="") r order title ) t1 on t1.id = t.id set t.title = concat(t1.title,case when rank = 0 "" else rank end) ;

or, more efficient way take take strawberry's reply , turn update statement in similar fashion....

hence:

update titles t inner bring together ( select x.id , concat(x.title,case when y.id not null count(y.id) else "" end) newtitle titles x left bring together titles y on y.title = x.title , y.id < x.id grouping x.id ) t1 on t1.id = t.id set t.title = t1.newtitle ;

mysql sql sql-update