CODEWITHSUNDEEP
sqlt-sqlmerge
Steve's a D
Steve's a D

Reputation: 3821

Using a join in a merge statement

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'.

Upvotes: 32

Views: 83397

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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.

Upvotes: 49

Related Questions