SQL Server update with left connection and group,

Question

I am doing an update for our database and want to update rows that do not have existing elements in another table. I can join the tables together, but I had problems with grouping the table to get a count of the number of rows

UPDATE dpt SET dpt.active = 0 FROM DEPARTMENT dpt LEFT JOIN DOCUMENTS doc on dpt.ID = doc.DepartmentID GROUP BY dpt.ID HAVING COUNT(doc.ID) = 0 

What should I do?

Answer 1

Using:

 UPDATE DEPARTMENT SET active = 0 WHERE NOT EXISTS(SELECT NULL FROM DOCUMENTS doc WHERE doc.departmentid = id) 

Answer 2

 UPDATE department SET active = 0 WHERE id NOT IN ( SELECT departmentId FROM doc )