使用ON DUPLICATE KEY UPDATE进行mysql更新

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.

这个工作正常,直到有一个用户连接到旧对象和merged_object。这会导致重复。我被卡住了,我可以想办法用多个查询和对象操作来做到这一点,但我真的想在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

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

无法添加或更新子行:外键约束失败(yourtable.user_object,CONSTRAINT FK_user_object_user_idx FOREIGN KEY(user_id)REFERENCES user(user_id))

2 个解决方案

#1

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

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

合并两个对象时,是否同时保留旧对象和合并对象,还是删除其中一个?如果删除合并到旧对象的新对象,则无需更新主键,因为旧对象引用仍然有效

#2

I think you want

我想你想要的

INSERT .... ON DUPLICATE KEY UPDATE"

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

#1