Update duplicate rows

Question

I have a table:

id    name
1     a
2     a
3     a
4     b
5     b
6     c

I am looking for an update statement that will update name column to:

id    name
1     a
2     a-2
3     a-3
4     b
5     b-2
6     c

In SQL Server I would use:

;with cte as(select *, row_number() over(partition by name order by id) rn from table)
update cte set name = name + '-' + cast(rn as varchar(10))
where rn <> 1

I am not strong in MySQL nonstandard queries. Can I do something like this in MySQL?

Answer 1

You can do this:

UPDATE YourTable p
       JOIN(SELECT t.id,t.name,count(*) as rnk
            FROM YourTable t
            INNER JOIN YourTable s on(t.name = s.name and t.id <= s.id)
            GROUP BY t.id,t.name) f
       ON(p.id = f.id)
SET p.name = concat(p.name,'-',f.rnk)
WHERE rnk > 1

This will basically use join and count to get the same as ROW_NUMBER() , and update only those who have more then 1 result(meaning the second,third ETC excluding the first)

Answer 2

In MySQL you can use variables in order to simulate ROW_NUMBER window function:

SELECT id, CONCAT(name, IF(rn = 1, '', CONCAT('-', rn))) AS name
FROM (
SELECT id, name,
       @rn := IF(name = @n, @rn + 1,
                 IF(@n := name, 1, 1)) AS rn
FROM mytable
CROSS JOIN (SELECT @rn := 0, @n := '') AS vars
ORDER BY name, id) AS t

To UPDATE you can use:

UPDATE mytable AS t1
SET name = (
   SELECT CONCAT(name, IF(rn = 1, '', CONCAT('-', rn))) AS name
   FROM (
      SELECT id, name,
             @rn := IF(name = @n, @rn + 1,
                       IF(@n := name, 1, 1)) AS rn
      FROM mytable
      CROSS JOIN (SELECT @rn := 0, @n := '') AS vars
   ORDER BY name, id) AS t2
   WHERE t1.id = t2.id)

Demo here

You can also use UPDATE with JOIN syntax:

UPDATE mytable AS t1
JOIN (
   SELECT id, rn, CONCAT(name, IF(rn = 1, '', CONCAT('-', rn))) AS name
   FROM (
      SELECT id, name,
             @rn := IF(name = @n, @rn + 1,
                       IF(@n := name, 1, 1)) AS rn
      FROM mytable
      CROSS JOIN (SELECT @rn := 0, @n := '') AS vars
      ORDER BY name, id) AS x
) AS t2 ON t2.rn <> 1 AND t1.id = t2.id
SET t1.name = t2.name;

The latter is probably faster than the former because it performs less UPDATE operations.