SQL query to merge users with same email

[Mr. Mikey]

I have several hundred users who have the same email address whom I want to merge together, I was wondering what the SQL query would be for it, I thought it could be something like;

UPDATE vb_user
SET username = X
WHERE email LIKE '%[email protected]%'

but I think that would only work on one user, and I have several hundred, I'm looking for a way other than "Merge Users" in the admincp, as it would take hours. I'm not adverse to doing it manually, however if there is a quicker way, naturally I will take it.

Again, I need to MERGE the users based on email address, the above query is most likely wrong because I couldn't find the SQL which merges the accounts.

Edit, I've found the SQL to LIST users with the same email address;

SELECT username, email FROM vb_user GROUP BY email HAVING COUNT(email) > 1

now how would I merge them? :/

[Dean C]

This is definitely not the approach you should follow. You certainly shouldn't be doing it via pure SQL due to the large amount of foreign keys which will reference the defunct user id's. A better way would be to look at the code behind the merge user action and write a script to do it in mass.

[Mr. Mikey]

The duplicate accounts all have 0 posts, so I don't have to worry about the defunct userid's referenced in posts.