因为,历史遗留问题之前允许一个email注册多个帐号。现在想要改为,1个email仅对应1个帐号(即唯一)。
希望对数据库进行更新,对于email字段相同的记录将之改为
concat(uid,'@old-email.local')
请教如何写sql语句
你好,我尝试了一下(数据库为mysql),sql如下:
update login a,
(select a.id
from login a,
(select email, count(id) as num
from login
group by email
having count(id) >= 2) b
where a.email = b.email) b
set a.email = concat(a.id, '@old-email.local')
where a.id = b.id
在来个oracle的
update login
set email = concat(id, '@old-email.local')
where email in (select email
from login
group by email
having count(*) > 1)
单一的sql实现起来比较复杂,可以考虑存储过程,先把重复的记录查询出来,游标遍历更新即可