CODEWITHSUNDEEP
mysqlreplaceduplicatesunique-key
busbina
busbina

Reputation: 579

mysql update with ON DUPLICATE KEY UPDATE

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))

Upvotes: 2

Views: 4326

Answers (2)

Stephen Senkomago Musoke
Stephen Senkomago Musoke

Reputation: 3523

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

Upvotes: 2

Jonathon Hibbard
Jonathon Hibbard

Reputation: 1546

I think you want

INSERT .... ON DUPLICATE KEY UPDATE"

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

Upvotes: 0

Related Questions