mysql更新查询，合并两个值

I have a table like this

我有一张这样的桌子

USER   |   DATA
----------------
User1  |   123
User1  |   456
User2  |   456
User3  |   123
User4  |   789

and i have a UNIQUE constraint for User-Data. Now i want to replace all "456" with "123", so in the end i'd have

我对User-Data有一个UNIQUE约束。现在我想用“123”替换所有“456”，所以最后我会有

USER   |   DATA
----------------
User1  |   123
User2  |   123
User3  |   123
User4  |   789

I really thought that it would be easy, u_U any idea how to proceed? any help would be appreciated =)

我真的觉得这很容易，你知道如何继续吗？任何帮助将不胜感激=）

Thxs

3 个解决方案

#1

UPDATE IGNORE yourtable SET `data`=123 WHERE `data`=456

Not so hard was it.

没那么难。

But how do you want to deal with this scenario:

但是你想如何处理这种情况：

USER   |  DATA
--------------
userx  |  123
userx  |  456

？

#2

"With the IGNORE keyword, the update statement does not abort even if errors occur during the update. Rows for which duplicate-key conflicts occur are not updated"

“使用IGNORE关键字，即使在更新期间发生错误，update语句也不会中止。发生重复键冲突的行不会更新”

So, Update any rows that would not violate the unique constraint. If any rows would, just delete them

因此，更新任何不违反唯一约束的行。如果有任何行，只需删除它们

UPDATE IGNORE Table SET data=123 WHERE data=456 
DELETE FROM Table WHERE data=456

#3

If you have a unique constraint, you can't have two entries with User1 - 123. So your last result is correct. You also can't have two entries with User2 - 456. Cause you have a unique constraint combined with both fields.

如果您有唯一约束，则不能有User1 - 123的两个条目。因此，您的上一个结果是正确的。用户2 - 456也不能有两个条目。因为您有一个与两个字段结合的唯一约束。

#1

UPDATE IGNORE yourtable SET `data`=123 WHERE `data`=456