mysql - SQL 更新在 JOIN 子查询中抛出错误

Question

我有一个帐户表，其中一行是 role_id。我有另一个名为组织组的表，它包含与组织关联的组，这个表有一个 ID 和一个名称列。我有一个名为 account_group 的第三个表，它有两列 account_id 和 group_id 将两者映射在一起。

现在我想检查帐户的组，并根据组名更新此帐户的角色 ID 值。

我设法想到的查询:

    UPDATE account 
SET account.role_id = (CASE WHEN organisation_group.name = "Social Worker" THEN 50
                            WHEN organisation_group.name = "Foster Carer" THEN 26
                       END)
    WHERE organisation_group.name in 
    (SELECT organisation_group.name FROM organisation_group 
INNER JOIN account_group ON organisation_group.id = account_group.group_id 
WHERE organisation_id = 11 GROUP BY organisation_group.name);

这将抛出以下错误 Unknown column 'organisation_group.name' in 'IN/ALL/ANY subquery' 。我不擅长 SQL，但我需要此更新。关于如何执行此操作的任何想法。

50 和 26 是我要使用的角色 ID。

更新 1(更清晰的解释)

我有一个包含列 [id,username,name,role_id,organisation_id] 的帐户表。

我需要编辑 account.role_id 列。我需要做的是:

1. 检查帐户的组名。

这些组位于名为 organisation_group 的表中。该表包含三列 [id,organisation_id,name]。

为了将帐户与组映射，我有一个名为 account_group 的表，其中包含三列 [id. account_id , group_id].

2- 根据组名，我需要更新 account.role_id 以在(组名 = 角色名)时具有角色 ID。

角色存储在名为 organisation_role 的表中，该表包含列 [id,name,organisation_id]。

Answer 1

首先，在您的 where 条件中，您指的是在顶层不可见的 table.column WHERE organization_group.name可能是你需要 account.name其次，一个建议，你没有聚合函数 i 子查询，所以如果你需要不同的值，请使用不同的子句

UPDATE account 
SET account.role_id = (CASE WHEN organisation_group.name = "Social Worker" THEN 50
                            WHEN organisation_group.name = "Foster Carer" THEN 26
                       END)
WHERE account.name in 
    ( SELECT distinct organisation_group.name FROM organisation_group 
      INNER JOIN account_group ON organisation_group.id = account_group.group_id 
      WHERE organisation_id = 11 
     );

但是如果我对你的加入规则理解正确，你应该以这种方式加入

UPDATE account 
INNER JOIN account_group on account_group.account_id = account.id
INNER JOIN organisation_group on organisation_group.id = account_group.group_id 
SET account.role_id = (CASE WHEN organisation_group.name = "Social Worker" THEN 50
                        WHEN organisation_group.name = "Foster Carer" THEN 26
                   END)
WHERE organisation_group.id = 11