开发者

SQL Server Update with left join and group by having

开发者 https://www.devze.com 2022-12-23 01:06 出处:网络
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 amhaving trouble grouping the table to get a co

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
        )
0

精彩评论

暂无评论...
验证码 换一张
取 消