help with query

[mark_nsx]

Hi all,

 

Say i have a table called `users_events` with fields `user_id` and `event_id` with

the following data:

user_id  |  event_id
-------------------
1          |      1
1          |      3
2          |      1
2          |      2
2          |      3
-------------------

 

What I'd like to do is merge rows with user_id = 2 into user_id = 1 and delete duplicate ones (event_id), which will

return the result:

 

user_id  |  event_id
-------------------
1          |      1
1          |      3
1          |      2
-------------------

 

Thanks in advance for the help! Cheers!

[Xurion]

First you'll need to make all your user_id that are 2 into 1:

 

UPDATE users_events SET user_id=1 WHERE user_id=2

 

Because there is no 'function' in mysql to remove duplicates, here is the simplest way to achieve it:

 

CREATE TABLE new_users_events(
  user_id INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(id),
  event_id INT)

INSERT INTO new_users_events(user_id, event_id)
   SELECT DISTINCT user_id, event_id FROM users_events;

DROP TABLE users_events;

RENAME TABLE new_users_events users_events;

 

I can't test this from where I am, but I think this will work.

[fenway]

Why not just delete the dupes?

[Xurion]

I assumed he had a lot of dupes and wanted to automate.

[mark_nsx]

once the site grows, there's a possibility of lots of dupes..

could there be a way of just combining an update, insert, and just select

statements? i think there's a huge overhead in creating a new table

and copying all those rows..

[Xurion]

Best way: put precautions in place to prevent dupes.

[fenway]

Or use a UNIQUE index if possible.

[mark_nsx]

the the table has precautions to prevent dupes..its just that

i have a merge function which generates those dupes..

[Xurion]

Edit the function to not include dupes?