php - MySQL Insert row, on duplicate: add suffixes to older (existing) values and re-insert -

i looking opposite of solution listed here:

mysql insert row, on duplicate: add suffix , re-insert

by: https://stackoverflow.com/users/296452/andreas-wederbrand

i want append suffixes older (existing) value(s) in table.

hence have result:

and if try insert dude again, trigger check if exists, append next available (-3 in case) existing one, , insert newest dude without suffix:

is possible?

thanks in advance. chris

a trigger in mysql cannot act on table caused fire, hence plan not work, @ least not described it. asking going difficult, if not impossible, do, , end being maintenance nightmare. don't see point labelling slug values versions. can offer workaround, namely building version suffix @ time query. painless, , frees having draconian table design. this:

set @rank = 0; set @slug = null;  select     t1.id,     case when t2.id null          concat(t1.slug, '-', cast(t1.label char(50)))          else t1.slug end slug (     select         id,         @rank:=case when @slug = slug @rank + 1 else 1 end label,         @slug:=slug slug     yourtable     order slug, id ) t1 left join (     select slug, max(id) id     yourtable     group slug ) t2     on t1.id   = t2.id ,        t1.slug = t2.slug; 

output:

demo here:

rextester