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开发者_StackOverflow dpt
LEFT JOIN DOCUMENTS doc on dpt.ID = doc.DepartmentID
GROUP BY dpt.ID
HAVING COUNT(doc.ID) = 0
What should I be doing?
Use:
UPDATE DEPARTMENT
SET active = 0
WHERE NOT EXISTS(SELECT NULL
FROM DOCUMENTS doc
WHERE doc.departmentid = id)
UPDATE department
SET active = 0
WHERE id NOT IN
(
SELECT departmentId
FROM doc
)
精彩评论