MYSQL: HAVING, GROUP BY in SQL UPDATE query

             I have to update the rows of a table in which a column value have unique count, ie the value of that column comes only once.


For eg: as per the table shown below:

     

            I want to update the value of b=1, for every rows having the count of value of column a is unique. Here the value a=3 comes in 6th row only, so update the value of that row.

             I can't use a HAVING COUNT(T1.a) = 1 in a update query. I also filed to use a IN in WHERE clause with a subquery. Finally I can use a INNER JOIN to achieve the updation.

The working SQL query as below:

UPDATE table1
JOIN (
      SELECT T1.id
        FROM table1 T1
        WHERE T1.c = 0
        GROUP BY T1.a
        HAVING COUNT(T1.a) = 1
     )T2 ON T2.id = table1.id
SET b = 1
WHERE 1 


NB: This is for my future reference only