MySQL 将 SELECT 命令转换为 UPDATE

Question

我的任务是通过将“ap_criteria”表中“value”列的值从 0 更改为 1 来更新我们的数据库。

我必须更改执行此操作时获得的特定组的这些值:

SELECT id, name, surname
FROM ap_volunteer 
WHERE medical_certificate = 1 
AND (id IN (SELECT DISTINCT(id_volunteer) 
FROM ap_criteria 
WHERE id_type = 177 
AND value = '0'))
ORDER BY name ASC 

我已经尝试了几次，但我在 SQL 方面的有限经验肯定会显示出来。这是我最近的尝试:

UPDATE `ap_criteria`
INNER JOIN ap_volunteer ON ap_criteria.id_volunteer = ap_volunteer.id
WHERE medical_certificate = 1
AND (id IN (SELECT DISTINCT(id_volunteer) 
FROM ap_criteria
AND id_type = 177 AND value = 0))
SET ap_criteria.value = 1

不幸的是，那个以失败告终!如果有人能帮助我并指出正确的方向，我将不胜感激。

提前致谢!

Answer 1

正确的语法是这样的:

UPDATE ap_criteria c JOIN
       ap_volunteer v
       ON c.id_volunteer = v.id
    SET c.value = 1
WHERE medical_certificate = 1 AND
      id IN (SELECT c2.id_volunteer 
             FROM ap_criteria c2 
             WHERE c2.id_type = 177 AND
                   c2.value = 0
            );

您会注意到我还添加了表别名、限定的列名(我可以知道它们来自哪里)，并从不需要的子查询中删除了 DISTINCT。

编辑:

在 MySQL 中，你需要使用一个JOIN:

UPDATE ap_criteria c JOIN
       ap_volunteer v
       ON c.id_volunteer = v.id JOIN
       (SELECT DISTINCT c2.id_volunteer 
        FROM ap_criteria c2 
        WHERE c2.id_type = 177 AND
              c2.value = 0
       ) c2
       ON v.id = c2.id_volunteer
    SET c.value = 1
WHERE medical_certificate = 1 ;