sql更新：通过在之前添加+id来消除电子邮件地址的重复@

xfyts7mz 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(336)

我正在尝试更新mysql中包含重复电子邮件地址和唯一自定义\u id的clients表。我想通过在@符号前添加“+”和customer \u id来更改重复的电子邮件地址，但仅适用于不唯一的电子邮件地址。

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

INPUT
+-----------+-------------------------+
| custom_ID | email                   |
+-----------+-------------------------+
|  1001     | john.smith@live.com     |
|  1002     |    evyandy@email.net    |
|  1007     |    evyandy@email.net    |
|  1012     |        ann@live.com     |
|  1020     |       rick@yahoo.com    |
|  1021     |        ann@live.com     |
|  1023     |    evyandy@email.net    |
|  1024     |       emma@gmail.com    |
+-----------+-------------------------+

 OUTPUT
+-----------+----------------------------+
| custom_ID | email                      |
+-----------+----------------------------+
|  1001     |   john.smith@live.com      |
|  1002     |      evyandy@email.net     |
|  1007     | evyandy+1007@email.net     |
|  1012     |          ann@live.com      |
|  1020     |         rick@yahoo.com     |
|  1021     |     ann+1021@live.com      |
|  1023     | evyandy+1023@email.net     |
|  1024     |         emma@gmail.com     |
+-----------+----------------------------+

sql mysql duplicates Email

来源：https://stackoverflow.com/questions/62164225/sql-update-deduplication-of-email-address-by-adding-id-before

1条答案

按热度按时间

vq8itlhq1#

你可以用 update 具有窗口功能和 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;

赞(0）回复(0）举报 2021-07-26

我来回答

sql更新：通过在之前添加+id来消除电子邮件地址的重复@

1条答案

相关问题

热门标签

最新问答