mysql查询帮助(合并行)

Question

由于程序中的错误，我的数据库中有一些半重复的数据。我想合并这些记录(或删除重复记录)。

我的数据是这样的:

usertable:
(userid, username, useremail)
101, joeuser, joeuser@mycompany
102, joeuser, joeuser@mycompany

datatable: 
(userid, datasubmitted)
101, mysubmittedata
102, othersubmitteddata

我想删除所有重复的 ID，并将任一 ID 的所有记录合并到一个用户 ID 中。

完成后我希望数据看起来像这样:

usertable:
(userid, username, useremail)
101, joeuser, joeuser@mycompany

datatable: 
(userid, datasubmitted)
101, mysubmittedata
101, othersubmitteddata

Answer 1

Its a two step process

1. fix your datatable first

Update datatable set userid = (select min(userid) from usertable group by username, useremail
    where username=datatable.username and useremail=datatable.useremail)



2. then remove duplicates from user table

delete from usertable u1 where userid > (select min(userid) from usertable u2 group by username, useremail
    where u1.username=u2.username and u1.useremail=u2.useremail)