sql - 带连接的SQL Server更新语句

我在加载表中有数据，并且正在尝试编写更新语句以填充其他自引用表，但是在使用准确语法时遇到了困难。

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

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

据此，我假设当我运行以下SupervisorID查询时，将使用这四个记录的PositionID列中的值填充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

这是怎么回事，如何按此方案中的描述用SupervisorID填充PositionID字段？

最佳答案

尝试这个...

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)