时间戳

首页 > 解决方案 > 当该值已存在于另一行中时，如何更新该唯一列的值？

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

问题描述

假设我有以下 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 的角色更新为“侦探”来完成。

但是是否有可能只用一个查询来实现这个结果？

标签： mariadb

解决方案

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

您可以创建一个UPDATE查询，用于根据or字段CASE计算列的正确值。您必须在查询中使用以确保在更新另一行之前必须设置现有的非空值，该行获取先前的字符串值。请参阅有关此的文档：rolenameidORDER BYUPDATENULLUPDATE

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

查询可能如下所示：

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”的行并找到“侦探”角色的所有者所在的行。查询可能如下所示：

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

推荐阅读