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

Question

I am looking to do the opposite of the solution listed here:

MySQL Insert row, on duplicate: add suffix and re-insert

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

I want to append the suffixes to the older (existing) value(s) in the table.

Hence I would have this result:

And if I try to insert dude again, the trigger will check if it exists, then append the next available (-3 in this case) to the existing one, and insert the newest dude WITHOUT a suffix:

Is this possible?

Thanks in advance. Chris

Answer 1

A trigger in MySQL cannot act on the table which caused it to fire, hence your plan will not work, at least not as you described it. What you are asking for is going to be very difficult, if not impossible, to do, and would probably end up being a maintenance nightmare. I actually don't see any point to labelling the slug values with versions. I can offer a workaround, namely just building the version suffix at the time you query. This is fairly painless, and frees you from having a draconian table design. Something like this:

SET @rank = 0;
SET @slug = NULL;

SELECT
    t1.id,
    CASE WHEN t2.id IS NULL
         THEN CONCAT(t1.slug, '-', CAST(t1.label AS CHAR(50)))
         ELSE t1.slug END AS slug
FROM
(
    SELECT
        id,
        @rank:=CASE WHEN @slug = slug THEN @rank + 1 ELSE 1 END AS label,
        @slug:=slug AS slug
    FROM yourTable
    ORDER BY slug, id
) t1
LEFT JOIN
(
    SELECT slug, MAX(id) AS id
    FROM yourTable
    GROUP BY slug
) t2
    ON t1.id   = t2.id AND
       t1.slug = t2.slug;

Output:

Demo here:

Rextester