将select语句与insert合并 [英] Merge insert with select statement

问题描述

这对我有用

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;

但是当我这样更改时，它不起作用，在最后一个select

But when i change like this, its not working, showing error near last select

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;

推荐答案

根据 MSDN文档

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

带有INSERT from SELECT的语法，例如:

The syntax with INSERT from SELECT like:

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

不允许！

我会尝试通过与Table3进行另一次合并来解决您的问题.

I would try to solve your problem doing another merge with Table3.

这篇关于将select语句与insert合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！