MySQL Transforming a SELECT command into an UPDATE

问题: I've been tasked with updating our Database by changing the value of the "value" column in the "ap_criteria" table from 0 to 1. I have to change those values for a specif...

问题:

I've been tasked with updating our Database by changing the value of the "value" column in the "ap_criteria" table from 0 to 1.

I have to change those values for a specific group that I get when executing this:

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 

I've had a few attempts but my limited experience with SQL is definitely showing. Here's my latest attempt :

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

Unfortunately that one ended in failure! I'd appreciate if someone could help me out and point me in the right direction.

Thanks in advance!


回答1:

The correct syntax looks like this:

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
            );

You'll notice that I also added table aliases, qualified column names (where I could tell where they come from), and removed DISTINCT from the subquery where it is not needed.

EDIT:

In MySQL, you need to use a 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 ;
  • 发表于 2019-01-16 16:56
  • 阅读 ( 181 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除