sql – 使用select语句合并插入

MERGE Table1 AS tgt
 USING (SELECT TOP 1 * FROM Table2,(SELECT itmid FROM Table3 WHERE id=@id) as a
        WHERE id = @id) AS src
ON (tgt.id = src.id)
            WHEN MATCHED THEN UPDATE SET qty = qty + @qty
            WHEN NOT MATCHED THEN
                INSERT itmid
                VALUES itmid;

但是当我这样改变它不工作,显示最近选择附近的错误

MERGE Table1 AS tgt
 USING (SELECT TOP 1 * FROM Table2
        WHERE id = @id) AS src
ON (tgt.id = src.id)
            WHEN MATCHED THEN UPDATE SET qty = qty + @qty
            WHEN NOT MATCHED THEN
                INSERT itmid
                SELECT itmid FROM Table3 WHERE id=@id;

解决方法

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ] 
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

来自SELECT的INSERT语法如下所示：

WHEN NOT MATCHED THEN
            INSERT itmid SELECT itmid FROM Table3 WHERE id=@id;

不允许！

我会尝试解决你的问题做另一个与表3的合并.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!