基于3个表更新列?-UpdatingaColumnbasedon3tables?

3  

My [revised] solution use one ALL subquery to check ItemStatusID condition:

我的[修改过的]解决方案使用一个全子查询来检查itemcontrollsid条件:

DECLARE @MyPOID INT = 1;

DECLARE @IndentHeader TABLE
(
    IndentID INT PRIMARY KEY
    ,StatusID INT NOT NULL
);
INSERT  @IndentHeader 
VALUES  (1,5);
INSERT  @IndentHeader 
VALUES  (2,5);
INSERT  @IndentHeader 
VALUES  (3,5);

DECLARE @IndentDetail TABLE
(
    IndentID INT NOT NULL
    ,ItemID INT NOT NULL
    ,ItemStatusID INT NOT NULL
    ,PRIMARY KEY(IndentID, ItemID)
);
INSERT  @IndentDetail
VALUES  (1,22,4);
INSERT  @IndentDetail
VALUES  (1,23,4);
INSERT  @IndentDetail
VALUES  (2,11,4);
INSERT  @IndentDetail
VALUES  (2,12,3);
INSERT  @IndentDetail
VALUES  (2,13,3);
INSERT  @IndentDetail
VALUES  (3,22,3);

DECLARE @POIndent TABLE
(
    POID INT
    ,IndentID INT NOT NULL
    ,ItemID INT NOT NULL
    ,ItemStatusID INT NOT NULL
);
INSERT  @POIndent 
VALUES  (1,1,22,4);
INSERT  @POIndent 
VALUES  (1,1,23,4);
INSERT  @POIndent 
VALUES  (1,2,11,4);
INSERT  @POIndent 
VALUES  (2,3,22,4);

SELECT  *
FROM    @IndentHeader h;
SELECT  *
FROM    @IndentDetail d;
SELECT  *
FROM    @POIndent po;

UPDATE  @IndentHeader 
SET     StatusID = CASE WHEN 4 = ALL(SELECT d.ItemStatusID FROM @IndentDetail d WHERE d.IndentID = h.IndentID) THEN 4 ELSE 3 END
FROM    @IndentHeader h
WHERE   h.IndentID IN (SELECT po.IndentID FROM @POIndent po WHERE po.POID = @MyPOID);

SELECT  *
FROM    @IndentHeader h;

2  

The gist of it is to

它的要旨是

• find the minimum ItemStatusID for each IndentID
• 找到每个IndentID最小的itemsid。
• Join this back with IndentHeader
• 加入IndentHeader
• Use these in an UPDATE FROM statement
• 在FROM语句的更新中使用这些

SQL Statement

UPDATE  IndentHeader
SET     StatusID = ihd.ItemStatusID
FROM    IndentHeader ih
        INNER JOIN (
            SELECT  ItemStatusID = MIN(id.ItemStatusID)
                    , ih.IndentID
            FROM    IndentHeader ih
                    INNER JOIN IndentDetail id ON id.IndentID = ih.IndentID
                    INNER JOIN POIndent pi ON pi.IndentID = id.IndentID
            WHERE   pi.POID = 1     
            GROUP BY
                    ih.IndentID     
        ) ihd ON ihd.IndentID = ih.IndentID         

Test script

;WITH IndentHeader (IndentID, StatusID) AS (
    SELECT 1, 5      
    UNION ALL SELECT 2, 5
)
, IndentDetail (IndentID, ItemID, ItemStatusID) AS (
    SELECT 1, 22, 4
    UNION ALL SELECT 1, 23, 4
    UNION ALL SELECT 2, 11, 4
    UNION ALL SELECT 2, 12, 3
    UNION ALL SELECT 2, 13, 3
)
, POIndent (POID, IndentID, ItemID, ItemStatusID) AS (
    SELECT 1, 1, 22, 4
    UNION ALL SELECT 1, 1, 23, 4
    UNION ALL SELECT 1, 2, 11, 4
)
--UPDATE    IndentHeader
--SET       StatusID = ihd.ItemStatusID
SELECT  ih.IndentID, ihd.ItemStatusID
FROM    IndentHeader ih
        INNER JOIN (
            SELECT  ItemStatusID = MIN(id.ItemStatusID)
                    , ih.IndentID
            FROM    IndentHeader ih
                    INNER JOIN IndentDetail id ON id.IndentID = ih.IndentID
                    INNER JOIN POIndent pi ON pi.IndentID = id.IndentID
            WHERE   pi.POID = 1     
            GROUP BY
                    ih.IndentID     
        ) ihd ON ihd.IndentID = ih.IndentID