带连接的 SQL Server 更新语句

Question

我在加载表中有数据，我正在尝试编写一个更新语句来填充一个额外的自引用表，但我对确切的语法有困难。

这是两个表格布局和随附数据的简化示例:

CREATE TABLE tmpLoad (
    PositionNumber   VARCHAR(2),
    SupervisorNumber VARCHAR(2)
    )

INSERT INTO tmpLoad VALUES ('01', '00')
INSERT INTO tmpLoad VALUES ('02', '01')
INSERT INTO tmpLoad VALUES ('03', '01')
INSERT INTO tmpLoad VALUES ('04', '03')
INSERT INTO tmpLoad VALUES ('05', '03')

CREATE TABLE tmpPosition (
    PositionID int,
    PositionNumber VARCHAR(2),
    SupervisorID int
    )

INSERT INTO tmpPosition VALUES (1, '01', null)
INSERT INTO tmpPosition VALUES (2, '02', null)
INSERT INTO tmpPosition VALUES (3, '03', null)
INSERT INTO tmpPosition VALUES (4, '04', null)
INSERT INTO tmpPosition VALUES (5, '05', null)

tmpLoad 中的数据代表五名员工，使用他们的 PositionNumber 作为唯一标识符，以及他们各自的主管。

• 员工 01 是老板(SupervisorNumber == 0)
• 员工 02 和 03 向员工 01 报告
• 员工 04 和 05 向员工 03 报告

tmpPosition 表是自引用的，其中 PositionID 可以在许多其他记录的 SupervisorID 列中。

如您所见，所有记录的 SupervisorID 列目前都为空。我正在尝试通过将这两个表连接在一起来用适当的 PositionID 填充它。

为了验证我的想法，我运行了以下 SELECT 查询:

 SELECT a.PositionNumber,
        a.SupervisorNumber,
        b.PositionID,
        b.PositionNumber,
        b.SupervisorID
 FROM tmpLoad a
      JOIN tmpPosition b 
      ON a.SupervisorNumber = b.PositionNumber

在我看来，它产生了预期的结果:

PositionNumber--SupervisorNumber--PositionID--PositionNumber--SupervisorID
02--------------01----------------1-----------01--------------NULL
03--------------01----------------1-----------01--------------NULL
04--------------03----------------3-----------03--------------NULL
05--------------03----------------3-----------03--------------NULL

据此，当我运行以下 UPDATE查询:

UPDATE tmpPosition
SET SupervisorID = b.PositionID
FROM tmpLoad a
     JOIN tmpPosition b 
     ON a.SupervisorNumber = b.PositionNumber

但是，在我运行查询之后，结果不是我所期望的:

SELECT *
FROM tmpPosition

PositionID--PositionNumber--SupervisorID
1-----------01--------------1
2-----------02--------------NULL
3-----------03--------------3
4-----------04--------------NULL
5-----------05--------------NULL

理想的结果是:

PositionID--PositionNumber--SupervisorID
1-----------01--------------NULL
2-----------02--------------1
3-----------03--------------1
4-----------04--------------3
5-----------05--------------3

这是怎么回事，我如何按照这种情况中的描述，用 PositionID 填充 SupervisorID 字段？

Answer 1

试试这个...

UPDATE tmpPosition
SET SupervisorID = tL.SupervisorNumber
FROM tmpPosition tp   
JOIN tmpLoad tl ON tp.PositionNumber = CONVERT(INT,tl.PositionNumber) 

这将准确给出您在此模式中想要的内容。我仍然不认为这是一个好的数据库设计。

UPDATE tmpPosition 
SET SupervisorID = CASE WHEN tl.SupervisorNumber = 0 
                    THEN NULL 
                    ELSE tl.SupervisorNumber 
                   END
FROM tmpPosition tp   
JOIN tmpLoad tl ON tp.PositionNumber = CONVERT(INT,tl.PositionNumber)