2

I have a table of foreign key and I am trying to merge duplicate records.

My table looks like this:

user_id | object_id

The table is a two column unique key, so there cannot be duplicates.

My query looks like this:

UPDATE user_object SET object_id = merge_obj_id WHERE object_id = old_object_id

This works fine until there is a user attached to both the old object and the merged_object. Which causes a duplicate. I'm stuck, I can think of ways to do this with multiple queries and object manipulation but I would really like to do this in SQL.

UPDATE: This might work?

INSERT INTO user_object (user_id,merge_object_id) SELECT user_id FROM user JOIN
user_object ON user.user_id = user_object.user_id WHERE object_id = old_object_id ON
DUPLICATE KEY (DELETE user_object WHERE user_object.user_id = user_id AND
user_object.user_id = old_object_id);`

UPDATE: Tried this:

INSERT user_object(user_id,object_id) SELECT 12345 as object_id, user.user_id as user_id FROM user JOIN user_object ON user.user_id = user_object.user_id
WHERE user_object.object_id = 23456

But it give me this error:

Cannot add or update a child row: a foreign key constraint fails (yourtable.user_object, CONSTRAINT FK_user_object_user_idx FOREIGN KEY (user_id) REFERENCES user (user_id))

busbina
  • 549
  • 1
  • 7
  • 19

2 Answers2

2

You could use

REPLACE INTO user_object(user_id,object_id) SELECT 12345 as object_id, user.user_id as user_id FROM user JOIN user_object ON user.user_id = user_object.user_id WHERE user_object.object_id = 23456

This will delete any old rows which match before inserting the new row.

When you merge two objects do you maintain both the old object and the merged one or do you remove one of them? If you delete the new object which was merged into the old one then there is no need to update the primary keys as the old object reference is still valid

Stephen Senkomago Musoke
  • 3,528
  • 2
  • 29
  • 27
0

I think you want

INSERT .... ON DUPLICATE KEY UPDATE"

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
Jonathon Hibbard
  • 1,547
  • 13
  • 20
  • I tried that one already but I don't have the user_id to insert. I could get it but it would require more queries. The only two bits of data I have are the merged_into_id and the old_object_id. So I cant do the insert because I am missing the other unique key. – busbina Mar 02 '12 at 21:08