sql - 使用 NULL 值更新 Select Join

Question

我有两个表 STR_IndentDetail 和 PUR_POIndent

STR_IndentDetail:

   IndentID   ItemID    POQty     D1      D2       D3        RD
  ---------   -------  ------    ----     ---     ---        ---
    2            1       NULL     10       20      30        NULL
    2            6       NULL     20       40      60        45

PUR_PO缩进:

   POID        IndentID      ItemID     Quantity     D1     D2       D3        RD
  ------      ----------     ------    ----------   ----    ---      ---       ---
    2            2             1           55         10     20      30        NULL
    2            2             6           100        20     40      60         45

我想使用 PUR_POIndent 表数量更新 STR_IndentDetail 表 POQty。我已经根据 INNER JOIN 和 LEFT OUTER 编写了两个更新语句。但两个查询仅更新一行，该行在 D1、D2、D3 和 RD 列中具有值。包含具有 NULL 值的 RD 列的行未获得更新。如何为此案例编写更新声明。以下是我的两条更新声明。

基于内部联接:

     UPDATE STR_IndentDetail
                SET
                    POQty = PUR_POIndent.Quantity
                FROM
                    PUR_POIndent
                WHERE
                    PUR_POIndent.IndentID           = STR_IndentDetail.IndentID AND
                    PUR_POIndent.ItemID             = STR_IndentDetail.ItemID   AND
                    PUR_POIndent.D1                 = STR_IndentDetail.D1       AND
                    PUR_POIndent.D2                 = STR_IndentDetail.D2       AND
                    PUR_POIndent.D3                 = STR_IndentDetail.D3       AND
                    PUR_POIndent.RD = STR_IndentDetail.RD 
                    AND PUR_POIndent.POID = 2

基于左连接:

 UPDATE STR_IndentDetail
                SET
                    POQty = PUR_POIndent.Quantity       
                FROM
                    STR_IndentDetail LEFT OUTER JOIN PUR_POIndent ON
                    PUR_POIndent.IndentID           = STR_IndentDetail.IndentID AND
                    PUR_POIndent.ItemID             = STR_IndentDetail.ItemID   AND
                    PUR_POIndent.D1                 = STR_IndentDetail.D1       AND
                    PUR_POIndent.D2                 = STR_IndentDetail.D2       AND
                    PUR_POIndent.D3                 = STR_IndentDetail.D3       AND
                    PUR_POIndent.RD = STR_IndentDetail.RD        WHERE 
                        PUR_POIndent.POID = 2

两个查询都会忽略 RD 值为 NULL 的行。我想更新这两行。这个怎么做？如有任何建议，请。

Answer 1

您不能将 NULL 值与 = 进行比较。更改条件以使用 ISNULL并传递一个表中不存在的值。

示例

ISNULL(PUR_POIndent.RD, -999) = ISNULL(STR_IndentDetail.RD, -999)

<强> NULL Comparison Search Conditions

Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, the following comparison always yields UNKNOWN when ANSI_NULLS is ON: