update duplicate email values except first one in the sql table-CodePudding

I have a table that contains column email, in that column I have a few duplicate values.

What I want is to update duplicate values adding the existing value BREAK string to it except the first one.

Example, before:

1email
1email
2email
3email
3email

after:

1email
1email BREAK
2email
3mail
3email BREAK

My table has a lot of columns, i just give simple example of what i want

I tried this code but its fixed and doesent work:

UPDATE users AS t1
   SET email = email   ' BREAK'
 WHERE rrn(t1) =
       (SELECT MAX(rrn(t2))
          FROM users AS t2
         WHERE email  = '[email protected]')

CodePudding user response：

You must use multiple-table UPDATE syntax.

UPDATE users t1
JOIN ( SELECT email, MIN(id) id
       FROM users t2
       -- WHERE email  IN ( {emails list} )
       GROUP BY email ) t3 ON t1.id > t3.id AND t1.email = t3.email
SET t1.email = CONCAT(t1.email, ' BREAK')
;

id is primary key or another unique-valued expression. In current query all rows with the same email except the row with minimal id value will be updated.