SQL update : deduplication of email address by adding + ID before @

I’m trying to update a clients table in mySQL containing duplicate email addresses and a unique custom_ID. I would like to change duplicate email addresses by adding a ‘+’ and customer_ID before the @ sign, but only for the email addresses that are not unique.

UPDATE clients SET email = REPLACE(email,'@', CONCAT('+',custom_ID,'@'))

INPUT
+-----------+-------------------------+
| custom_ID | email                   |
+-----------+-------------------------+
|  1001     | [e-mail]     |
|  1002     |    [e-mail]    |
|  1007     |    [e-mail]    |
|  1012     |        [e-mail]     |
|  1020     |       [e-mail]    |
|  1021     |        [e-mail]     |
|  1023     |    [e-mail]    |
|  1024     |       [e-mail]    |
+-----------+-------------------------+

 OUTPUT
+-----------+----------------------------+
| custom_ID | email                      |
+-----------+----------------------------+
|  1001     |   [e-mail]      |
|  1002     |      [e-mail]     |
|  1007     | [e-mail]     |
|  1012     |          [e-mail]      |
|  1020     |         [e-mail]     |
|  1021     |     [e-mail]      |
|  1023     | [e-mail]     |
|  1024     |         [e-mail]     |
+-----------+----------------------------+

Answer

You can use update with window functions and a join:

update clients c join
       (select c.*, row_number() over (partition by email order by custom_id) as seqnum
        from clients cc
       ) cc
       on c.custom_id = cc.custom_id
    set email = concat(substring_index(email, '@', 1), '+', custom_id, '@', substring_index(email, '@', -1))
    where cc.seqnum > 1;