How to correctly add an INNER JOIN to a SQL Server MERGE statement? [closed]

eufgjt7s 于 2023-05-28 发布在 SQL Server

关注(0)|答案(2)|浏览(126)

Closed. This question needs debugging details . It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem . This will help others answer the question.

Closed 5 days ago.
Improve this question

I am doing a merge on 2 tables however on the insert statement I want to introduce a join statement. Please see below example

I tried the below but its giving me an error

MERGE table A 
using table B
WHEN MATCHED THEN
UPDATE .....
WHEN NOT MATCHED THEN
INSERT (Col1,Col2,Col3,Col4,Col5)
SELECT (B.Col1,C.Col7,B.Col3,D.Col1,B.Col4)
FROM table B
INNER JOIN Table C
on B.Col1 = C.Col1
INNER JOIN Table D
on C.Col5 = D.Col5

The above gives a n error,what is the correct syntax for the above?

sql-server

来源：https://stackoverflow.com/questions/76307532/how-to-correctly-add-an-inner-join-to-a-sql-server-merge-statement

2条答案

按热度按时间

bqjvbblv1#

You can use a CTE or derived table, or a view, as the source or target of the MERGE . The target must follow the rules for Updatable Views.

Do not put extra conditions into the main ON of the MERGE as you may get unexpected results.

WITH Source AS (
    SELECT B.*
    FROM table B
    INNER JOIN Table C on B.Col1 = C.Col1
    INNER JOIN Table D on C.Col5 = D.Col5
)
MERGE table A
USING Source AS B
ON conditionsHere
WHEN MATCHED THEN
  UPDATE .....
WHEN NOT MATCHED THEN
  INSERT (Col1,Col2,Col3,Col4,Col5)
  VALUES (B.Col1,C.Col7,B.Col3,D.Col1,B.Col4);

赞(0）回复(0）举报 2023-05-28

vshtjzan2#

The syntax doesn't allow joins, so all the data need to be available upfront. Easiest is to use a subquery as source something like:

merge tablet as target
using (
   select a,b,c
   from tableb b 
   inner join tablec c
       ON c.id = b.id
 ) as source
 ON source.id = target.id
...

One of the problems is that joins might introduce complications like duplicates. For this and other reason, i would say avoid the merge statement and do it in two steps

赞(0）回复(0）举报 2023-05-28

我来回答

How to correctly add an INNER JOIN to a SQL Server MERGE statement? [closed]

2条答案

相关问题

热门标签

最新问答