mysql - UPDATE 查询中自引用 MySQL 表

Question

我有以下情况:

涉及3张表，‘患者’、‘用户’和‘中心’。它们具有以下属性和关系:

“病人”:

• pid = 主键
• doc = “users”的“uid”的外键
• localid = 默认设置为“-1”

“用户”:

• uid = 主键
• cid = 'center' 的 'cid' 的外键

“中心”:

• cid = 主键

患者不会直接分配到某个中心。相反，它们被分配给特定用户，而该用户又被分配给一个中心。

所有患者的患者“localid”当前均设置为“-1”。我正在尝试提出一个 SQL 查询，该查询会增加所有患者的“localid”，但仅限于通过用户间接关联的中心内。

本质上，我希望所有患者在其中心内都有一个唯一的 ID，但每个中心的 ID 应该从 0 开始。

这是我尝试过的查询，我认为它接近我需要的:

UPDATE patient p1
SET p1.localid=(SELECT (max(p2.localid) +1)
                FROM patient p2
                WHERE (SELECT u1.cid
                       FROM users u1
                       WHERE u1.uid=p1.doc)=(SELECT u2.cid
                                             FROM users u2
                                             WHERE u2.uid=p2.doc));

这会导致 SQL 错误“您无法在 FROM 子句中指定用于更新的目标表 'p1'”，因为 'p1' 在 WHERE 子句中引用自身。

我可以通过在 PHP 中使用多个 SQL 查询和循环来轻松解决这个问题，但我想知道是否可以仅使用普通 SQL 来解决这个问题？

Answer 1

您可以使用子查询中的条件将表JOIN到其自身，并为其指定一个别名，而不是使用子查询。这应该允许您更新正在查询的同一个表。

类似...

UPDATE patient p1
SET p1.localid = p2.newlocalid
INNER JOIN (
    SELECT pid, MAX(localid) + 1 AS newlocalid
    FROM patient p2
    WHERE...
) p2 ON p1.pid = p2.pid