get NOT unique values, display and condense

[kender]

I did search, trust me.. in the forums and the web

I have a database, the creator didn't care about any type of anything... so I have 900K "users" but this also includes people who forgot their password and created a new account...

What I am trying to do is reduce this and get rid of the duplicates... but i need to do it rationally

first I want to find all records where the email address is duplicated and display them along with the unique ID
then I need to alter any refrence to the duplicated id's, making them all match a single ID
then I need to delete the duplicate records

example

USERS
U_ID,  NAME,  EMAIL
110,   tom,   [e-mail]
9865,  tommy, [e-mail]
47472, tbone, [e-mail]

ORDERS
O_ID,   U_ID,  ITEM
918,    110,   widget
1858,   110,   widget2
1938,   9865,  widget3
4438,   9865,  widget4
4555,   9865,  widget5
6684,   9865,  widget6
163468, 47472, widget7
175684, 47472, widget8

COMMENTS
C_ID,  O_ID,   U_ID,  COMMENT
158,   918,    110,   "GREAT!"
1883,  4555,   9865,  "Had a problem, but resolved"
16548, 175684, 47472, "Wicked"

Notice, the user email is what I am trying to match here, I could care less wat he wants to call himself... I need to find out that he is USER 110, 4862 & 47472 then alter the tables for ORDERS and COMMENTS, changing ALL U_ID values to 47472 (last record) then delete from my database users 110 and 4862

I hope this makes sence, if you need any more details please feel free to ask... I honstly have NO IDEA where to start with this one...

[Crowly]

I think you should be able to find duplicates this way, untestet

SELECT u_id, email
FROM users
WHERE COUNT(DISTINCT(email))>1

But i dont think this result is the best to work of, this might be better

SELECT MAX(u_id) u_id, MAX(email) email
FROM users
WHERE COUNT(DISTINCT(email))>1
GROUP BY email

Should return one entry for email which are registered more than once.

If my head is working correctly i think this should delete all users who have duplicate emails, but keep latest/max user id. Its untestet, dont try it unless its on a test sample or you have backups.

DELETE FROM users u1
WHERE u1.u_id IN (SELECT u2.u_id
                  FROM users u2
                  WHERE COUNT(DISTINCT(u2.email))>1)
	  AND u1.u_id NOT IN (SELECT MAX(u3.u_id)
				          FROM users u3
                          WHERE COUNT(DISTINCT(u3.email))>1
                          GROUP BY u3.email)

I think something similar can be done with the UPDATE sql, im just not able to wrap my head around it atm 🙁 Or you could do it in php working of the first or 2nd sql.

[kender]

for the second one I get

#1111 - Invalid use of group function

[Crowly]

Thought that would work 🙁, but this does, i even testet it 😉

SELECT MAX(u1.u_id) u_id, MAX(u1.email) email 
FROM users u1
WHERE (SELECT COUNT(*) FROM users u2 WHERE u1.email=u2.email)>1 
GROUP BY u1.email

// alternativly like this
SELECT id, email 
FROM (SELECT MAX(u_id) id, MAX(email) email, COUNT(*) num 
      FROM users 
      GROUP BY email) duplicates 
WHERE num>1

It will return one row for each duplicate email, and the highest u_id for that email.

[kender]

database times out while running the process on both queries

any chance of doing it with a php script and a foreach or while statement to keep it going piece by piece? using a temporary database or something?

chances are, out of these 900k records there are 20 people with 20 or more accounts per person, 50 with 10 or more, hundreds with 5 or more, thousands with 2

[Crowly]

Sorry for a late reply, been on a trip to Switcherland for the last 4 days.

kender;10960932 wrote:

database times out while running the process on both queries

Where do you run the query? In php? If so php has a default timeout of 30 seconds. You can change this either in the config fila or with set_time_limit().
I think the 2nd query is the fastest.

any chance of doing it with a php script and a foreach or while statement to keep it going piece by piece? using a temporary database or something?

chances are, out of these 900k records there are 20 people with 20 or more accounts per person, 50 with 10 or more, hundreds with 5 or more, thousands with 2

Isnt a problem handling it in php, but you still need to find all the users with more than one account, unless you want to loop through them all and check if that user has duplicate accounts, which i think will be less effective.
I can see if im able to come up with some basic php code for this, but i caught a cold on my trip, and now my head is all mushy :queasy: so it might take a little while.

Also where you just run the query to find duplicate users, or an entire script to clean up the database, this is something that will probably run for a very long time due to the size.

For future reference you should be using unique indexes or other functions so you avoid duplicate entries where there shouldnt be any 😉