CODEWITHSUNDEEP
mysqlsql
Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Update duplicate rows

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?

Upvotes: 4

Views: 123

Answers (3)

Mita
Mita

Reputation: 422

The next query will do it 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)

Upvotes: 2

sagi
sagi

Reputation: 40491

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)

Upvotes: 4

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

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.

Upvotes: 4

Related Questions