mysql - 根据条件更新现有表列

Question

我正在尝试为下表编写 SQL 查询，

员工

id | name   | batch |certificate_name | flag
---------------------------------------
  1| stefan | a     | java            | null
  2| ross   | b     | c++             | null
  3| mad    | a     | php             | null

查询是，对于每一行检查证书的出现次数，如果证书在表中出现一次且批处理为“a”，则将标志设为 1，否则不执行任何操作

结果应该是:

id | name   | batch |certificate_name | flag
---------------------------------------
  1| stefan | a     |java             | 1
  2| ross   | b     |c++              | null
  3| mad    | a     |php              | 1

我尝试过以下内容，

update employee set flag = 1 
where batch = 'a' 
-- how to write sql query condition for check current row certificate occurrence in table
and ??? 

Answer 1

我首先会构建一个唯一证书列表。

SELECT certificate_name FROM Employee GROUP BY certificate_name HAVING count(*) = 1;

接下来我将在进行更新时使用它。

UPDATE Employee
SET flag = 1
WHERE batch = 'a'
 and certificate_name IN (SELECT certificate_name FROM Employee GROUP BY certificate_name HAVING count(*) = 1);