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:

1. Is this allowed? To use the select in the using statement? I'm getting a syntax error on line 1
2. Is there a better way to go about this? Am I making this more complicated than it has to be?
3. 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'.

Answer 1

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.