This works for me
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;
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;
According to MSDN docs
<merge_not_matched>::=
{
INSERT [ ( column_list ) ]
{ VALUES ( values_list )
| DEFAULT VALUES }
}
The syntax with INSERT
from SELECT
like:
WHEN NOT MATCHED THEN
INSERT itmid SELECT itmid FROM Table3 WHERE id=@id;
Is not allowed!
I would try to solve your problem doing another merge with Table3
.