CODEWITHSUNDEEP
sql-servermergesql-updatesql-insert
user1673665
user1673665

Reputation: 522

SQL MERGE from two tables as source

I've three tables and want to MERGE into table customer. But as source I need two tables: adress and ascust. adress and ascust are linked by column custnr like this:

SELECT adress.custnr, ascust.masternr
FROM adress INNER JOIN ascust 
ON adress.custnr = ascust.custnr

This is my current MERGE statement:

MERGE INTO dbo.customer d USING dbo.adress s1 on d.number = s1.custnr and d.lfdnr = s1.lfdnr
WHEN MATCHED THEN UPDATE SET d.name = s1.name, d.masternr = s2.masternr
WHEN NOT MATCHED THEN INSERT (name, masternr) VALUES (s1.name, s2.masternr);

As you can see, I need to UPDATE and INSERT column masternr from s2 (table ascust). How can I include s2 (table ascust) in my MERGE statement?

Upvotes: 1

Views: 2337

Answers (3)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Your query should be like this :

MERGE INTO dbo.customer d USING (SELECT adress.custnr, ascust.masternr
FROM adress INNER JOIN ascust 
ON adress.custnr = ascust.custnr) s1 on d.number = s1.custnr and d.lfdnr = s1.lfdnr
WHEN MATCHED THEN UPDATE SET d.name = s1.name, d.masternr = s2.masternr
WHEN NOT MATCHED THEN INSERT (name, masternr) VALUES (s1.name, s2.masternr);

Upvotes: 1

user1673665
user1673665

Reputation: 522

Thanks to Dave Brown for the hint with CTE. It works great.

WITH cte1 AS
(       
    SELECT adress.custnr, ascust.masternr, adress.lfdnr
    FROM adress INNER JOIN ascust 
    ON adress.custnr = ascust.custnr
)
MERGE INTO dbo.customer d USING cte1 s on d.number = s.custnr and d.lfdnr = s.lfdnr
WHEN MATCHED THEN UPDATE SET d.name = s.name, d.masternr = s.masternr
WHEN NOT MATCHED THEN INSERT (name, masternr) VALUES (s.name, s.masternr);

Upvotes: 2

Shekar Kola
Shekar Kola

Reputation: 1297

You may want to do like this:

MERGE INTO dbo.customer d USING 
                (SELECT adress.custnr, adress.lfdnr, ascust.masternr
                 FROM adress INNER JOIN ascust 
                            ON adress.custnr = ascust.custnr
                ) s1 on d.number = s1.custnr and d.lfdnr = s1.lfdnr

WHEN MATCHED THEN UPDATE SET d.name = s1.name, d.masternr = s1.masternr
WHEN NOT MATCHED THEN INSERT (name, masternr) VALUES (s1.name, s1.masternr);

Upvotes: 3

Related Questions