mysql - Update table using dynamically determined values -

i'm using mysql server in current project , need provide migration. have table 'patterns' field 'title' , new field named 'alias',which value null rows. need write in field using next algorithm:

1) if title field unique: write it's value in alias 2) if title not unique: alias = title +'-'+ n , n number of  occurrence 

for example:

________________  |id|title|alias |1 |smile|null |2 |smile|null |3 |smile|null ________________ 

should transformed to:

 ________________   |id|title|alias  |1 |smile|smile  |2 |smile|smile-1  |3 |smile|smile-2  ________________ 

is possible achieve such result using sql, thank in advance help

this pain in mysql. can using variables.

select t.*,        (case when tt.cnt > 1 concat(t.title, '-', rn) else t.title end) new_title (select t.*,              (@rn := if(@t = title, @rn + 1,                         if(@t := title, 1, 1)                        )              ) rn       t cross join            (select @rn := 0, @t := '') params       order title      ) t join      (select title, count(*) cnt       t       group title      ) tt      on tt.title = t.title; 

in many cases, can leave out "-1" on singleton. simplifies query:

select t.*,        (case when rn > 1 concat(t.title, '-', rn) else t.title end) new_title (select t.*,              (@rn := if(@t = title, @rn + 1,                         if(@t := title, 1, 1)                        )              ) rn       t cross join            (select @rn := 0, @t := '') params       order title      ) t ;