当一个唯一列的值已经存在于另一行中时，如何更新该值

Question

假设我有下面的MariaDB表；角色；必须是唯一的或空的：

id    |    role   |   name
-----------------------------
1     | null      | David
2     | spy       | Mike
3     | detective | Alan
4     | null      | George

我想达到以下状态：

id    |    role   |   name
-----------------------------
1     | null      | David
2     | spy       | Mike
3     | null      | Alan
4     | detective | George

我知道这可以通过首先将角色3更新为null，然后将角色4更新为"0"来实现；"侦探"；。

但是，只需要一个查询就可以实现这个结果吗？

Answer 1

以下在MySQL 8中是可能的，但我不知道它是否也适用于MariaDB：

可以创建UPDATE查询，该查询使用CASE根据name或id字段计算role列的正确值。您必须在UPDATE查询中使用ORDER BY，以确保在更新获取上一个字符串值的另一行之前，必须将现有的非null值设置为NULL。请参阅UPDATE的相关文档：

如果UPDATE语句包含ORDERBY子句，则按该子句指定的顺序更新行。这在某些可能导致错误的情况下非常有用。

查询可能如下所示：

UPDATE
assignment
SET
role = CASE name
WHEN 'Alan' THEN NULL
WHEN 'George' THEN 'detective'
END
WHERE
name IN ('Alan', 'George')
ORDER BY
name = 'Alan' DESC, -- Alan first
name = 'George' DESC; -- George second

如果你不知道以前的"；所有者；对于角色，您可以更改WHERE条件和CASE语句以查找"George"的行和"detective"角色所有者的行。查询可能如下所示：

UPDATE
assignment
SET
role = CASE name
WHEN 'George' THEN 'detective'
ELSE NULL
END
WHERE
name = 'George' OR -- the target
role = 'detective' -- the previous owner
ORDER BY
name = 'George' ASC; -- first row will be the previous owner
-- second row will be the target

Answer 2

让我们思考一个2表解决方案：

CREATE TABLE users (
user_id ...,        
name ...,
...
PRIMARY KEY(user_id)
)
CREATE TABLE roles (
role_name ...,
user_id ... COMMENT "The one user with this role",
PRIMARY KEY(role_name),
INDEX(user_id)    -- (or it could be UNIQUE)
)

为用户获取角色：

SELECT users.name,
roles.role_name   -- This will be NULL if not assigned to this user
FROM users
LEFT JOIN roles  USING(user_id)
WHERE ...;

改变角色(最初的问题(：

UPDATE roles
SET user_id = ?
WHERE role = ?;