Using a join in a merge statement

Question

Problem

Table 1:

| KeyColumn | DataColumn1 | DataColumn2|  
   01         0.1          0.2
   02         0.13         0.41

Table 2:

| anotherKey | DataColumn1 | DataColumn2|      
   A1          .15          1.2
   A2          .25          23.1

Table 3:

|KeyColumn| anotherKey |       
  01        A1
  02        A1

Given a key (A1, or A2) I need to update the DataColumn1 and DataColumn2 columns in table 1 with the corresponding values in table 2.

So table1 can have x number of rows updated, as shown in the above data. If I want to update A1, both 01 and 02 rows should be updated

(so the values in table1 would be 0.15 for datacolumn1 and 1.2 for datacolumn2 on both keys 01 and 02)

What I have tried so far:

MERGE table1
USING (SELECT *
       FROM table2
       LEFT OUTER JOIN table3
           on table2.anotherKey = table3.anotherKey
       WHERE table2.anotherKey = 'A1') tmpTable
ON 
   table1.keyColumn = tmpTable.keyColumn
WHEN MATCHED THEN
       UPDATE
       SET table1.DataColumn1 = tmpTable.DataColumn1
            ,table1.DataColumn2 = tmpTable.DataColumn2;

Questions:

Is this allowed? To use the select in the using statement? I'm getting a syntax error on line 1
Is there a better way to go about this? Am I making this more complicated than it has to be?
What am I doing wrong?

and the error:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'a'. Msg 102, Level 15, State 1, Line 12 Incorrect syntax near 'd'.

Why are you using a merge? Looks like this could be done with an update instead. — Mikael Eriksson, Oct 15 '12 at 20:32
I have no idea, I read a post on SO that was along the same lines as this (without the outer join) and it said that a merge is better suited. If you could provide an example on how to achieve this with only updates I would be forever greatful. — Steve's a D, Oct 15 '12 at 20:37
If there is no match, I need to insert it. I haven't gotten that far in the query yet as I just wanted to get the first part working. This is one of the reasons I also chose merge. Am I still going in the wrong direction? — Steve's a D, Oct 15 '12 at 20:42
Looks like you want to add rows to both `table2` and `table3` if there are missing rows. That is not possible with one merge statement. There can basically only be one target table. — Mikael Eriksson, Oct 15 '12 at 21:03
Merge does not scale as well as you think on large data sets. — Woot4Moo, Oct 15 '12 at 21:04
@Mikael no, only add a row to table1, if there is a row in table3 that says there should be an 03 with A1 — Steve's a D, Oct 15 '12 at 21:10
@Woot4Moo so I should just break this into a few smaller statements and not use merge? — Steve's a D, Oct 15 '12 at 21:11
Let me walk through this later tonight and I can post an answer that has decent performance for large data sets. — Woot4Moo, Oct 16 '12 at 12:35

score 49 · Accepted Answer · edited Mar 30 '21 at 14:46

The query you have will give the error

Msg 8156, Level 16, State 1, Line 59 The column 'AnotherKey' was specified multiple times for 'tmpTable'.

That is because you are using * in the using clause and AnotherKey is part of both table2 and table3.
Specify the columns you need. Also there is no use to have a outer join in there since you are using keycolumn in the onclause.

MERGE table1
USING (SELECT table3.keycolumn,
              table2.DataColumn1,
              table2.DataColumn2
       FROM table2
       INNER JOIN table3
           ON table2.anotherKey = table3.anotherKey
       WHERE table2.anotherKey = 'A1') tmpTable
ON 
   table1.keyColumn = tmpTable.keyColumn
WHEN MATCHED THEN
       UPDATE
       SET table1.DataColumn1 = tmpTable.DataColumn1
            ,table1.DataColumn2 = tmpTable.DataColumn2;

Update

Posting the actual error is always helpful.

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'a'. Msg 102, Level 15, State 1, Line 12 Incorrect syntax near 'd'.

Looks like you are on SQL Server 2005. Merge is avalible from SQL Server 2008.

You can check your SQL Server version with select @@version.

In my actual query I didn't use the * in the using clause. I'll post the actual query as it looks like I'm doing the same as you — Steve's a D, Oct 15 '12 at 21:02
I have same kind of problem.., but My condition for joining and merging invovles same column. Now how to resolve it? — Giri Prasad, Jun 10 '15 at 06:49

Using a join in a merge statement

1 Answers1

Linked