sql server 2005 中的更新语句 [英] Update statement in sql server 2005

问题描述

考虑下面的Dig，假设所有三个表都有一列Is_Deleted，默认设置为0...我想更新Is_Deleted=1 Customers 表的字段，其中 CustId=2 仅当包含 CustId=2 和 Is_Deleted=1 的行时 在 Orders 和 OrderItems表格...我不想使用 Cascade 选项.. 任何建议

Consider the following Dig, Assume that all the three tables have a column Is_Deleted by default it is set to 0... I want to update Is_Deleted=1 field of Customers table where CustId=2 only when the rows containing CustId=2 and Is_Deleted=1 in Orders and OrderItems Tables... I dont want to use Cascade option.. Any suggestion

_{(来源:microsoft.com)}

推荐答案

最简单的方法是 EXISTS.我假设您想同时检查 Orders 和 OrderItems.这也意味着您只能过滤一次 CustID.

Easiest way is EXISTS. I assume you want to check both Orders and OrderItems. This also means you only filter on CustID once.

UPDATE
   C
SET
   IsDeleted = 1
FROM
   Customers C
WHERE
   C.CustID = 2
   AND
   EXISTS (SELECT *
        FROM
            Orders O
        WHERE
            O.CustID = C.CustID AND O.IsDeleted = 1)
   AND
   EXISTS (SELECT *
        FROM
            Orders O
            JOIN
            OrderItems OI ON O.OrderID = OI.OrderID
        WHERE
            O.CustID = C.CustID AND OI.IsDeleted = 1)

这篇关于sql server 2005 中的更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！