SQL Server Update with left join and group by having

sql, sql-server, sql-update, t-sql

Solution

Use:

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

Problem

I'm making an update to our database and would like to update rows that do not have existing items in another table. I can join the tables together, but am having trouble 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 be doing?

Original source