CODEWITHSUNDEEP
mysql
SylanderXS
SylanderXS

Reputation: 46

mysql query help (merge rows)

Due to a bug in a program I've got some semi-duplicate data in my database. I'd like to merge those records (or delete duplicates).

My data looks like this:

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

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

I would like to get rid of any duplicate id's and merge any records for either id into a single userid.

When complete I'd like for the data to look like this:

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

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

Upvotes: 0

Views: 157

Answers (1)

d-live
d-live

Reputation: 8036

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)

Upvotes: 5

Related Questions