SQL Server 从另一张表取值更新目标表的语法错误求助

原语句错误说明 #

1. SQL Server 不支持直接在 UPDATE 关键词后写 JOIN 逻辑，关联关系需要写在 FROM 子句中
2. ON 子句仅用于定义表之间的匹配条件，不能写字段赋值逻辑
3. 语句中间多余的分号提前终止了SQL结构，导致语法报错

一、现有数据一次性更新 #

当前两张表没有预设的关联字段，我们通过排序后按序号匹配的方式实现两条记录的对应更新，SQL如下：

WITH OrderedBus AS (
    -- 给tblBus的记录按bus号升序排序，生成匹配序号
    SELECT 
        bus,
        rb_id,
        roadside_bottom,
        ROW_NUMBER() OVER(ORDER BY bus) AS row_num
    FROM tblBus
),
OrderedRB AS (
    -- 给tblRB_ID的记录按rb_id2升序排序，生成匹配序号
    SELECT 
        rb_id2,
        rb_name,
        ROW_NUMBER() OVER(ORDER BY rb_id2) AS row_num
    FROM tblRB_ID
)
UPDATE b
SET 
    b.rb_id = r.rb_id2,
    b.roadside_bottom = r.rb_name
FROM OrderedBus b
INNER JOIN OrderedRB r ON b.row_num = r.row_num;

执行后查询SELECT * FROM tblBus即可得到你需要的预期结果。

二、后续新增记录自动同步 #

如果需要tblRB_ID新增记录时自动同步到tblBus，可以通过触发器实现，示例如下：

CREATE TRIGGER trg_RBID_SyncToBus
ON tblRB_ID
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- 此处bus号按最大已有bus号自增生成，你可以根据实际业务规则调整生成逻辑
    DECLARE @LatestBus INT = ISNULL((SELECT MAX(CAST(bus AS INT)) FROM tblBus), 999);

    INSERT INTO tblBus(bus, rb_id, roadside_bottom)
    SELECT 
        CAST(@LatestBus + ROW_NUMBER() OVER(ORDER BY rb_id2) AS VARCHAR(10)),
        rb_id2,
        rb_name
    FROM inserted;
END

如果你的业务中bus号是由其他逻辑生成，修改触发器中bus字段的赋值规则即可。

内容的提问来源于stack exchange，提问作者LongLouis