Updating 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 update instructions that will update a column nameso that:

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

In SQL ServerI 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 good at MySQLcustom requests. Can I do something like this in MySQL?

Answer 1

You can do it:

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 mainly use concatenation and counting to get the same as ROW_NUMBER (), and only update those with more than 1 result (which means second, third ETC excluding the first)

Answer 2

In MySQL, you can use variables to mimic a window function ROW_NUMBER:

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

UPDATE :

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)

UPDATE JOIN:

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;

, , , , UPDATE.

Answer 3

The following query will do this with less effort for the database:

UPDATE
    tab AS tu
INNER JOIN
    --  result set containing only duplicate rows that must to be updated
    (
        SELECT
            t.id,
            COUNT(*) AS cnt
        FROM
            tab AS t
        --  join the same table by smaller id and equal value. That way you will exclude rows that are not duplicated
        INNER JOIN
            tab AS tp
        ON
            tp.name = t.name
        AND
            tp.id < t.id
        GROUP BY
            t.id
    ) AS tc
ON
    tu.id = tc.id
SET
    tu.name = CONCAT(tu.name, '-', tc.cnt + 1)