silly questions silly questions - 5 days ago 6
MySQL Question

update existing table column based on condition

I am trying to write SQL query for following table,

Employee

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


Query is, for each row check occurrences of certificate and if certificate occurs once in table and batch is 'a' then make flag 1 else do nothing

Result should be:

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


what I tried is following,

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

Answer

I would first build a list of unique certificates.

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

Next I would use that when doing an update.

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