在合并语句中使用联接 [英] Using a join in a merge statement

问题描述

问题

表1:

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

表2:

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

表3:

|KeyColumn| anotherKey |       
  01        A1
  02        A1

给出一个键(A1或A2)，我需要使用表2中的相应值来更新表1中的DataColumn1和DataColumn2列.

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.

因此，如上面的数据所示，table1可以更新x的行数.如果要更新A1，则01和02行都应更新

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

(因此，表01中的值对于键01和02的datacolumn1分别为0.15和datacolumn2为1.2)

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

到目前为止，我已经尝试过:

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;

问题:

1. 可以吗?要在using语句中使用select?我在第1行出现语法错误
2. 是否有更好的方法来解决此问题?我使事情变得比必须的复杂吗?
3. 我在做什么错了?

和错误:

第15层，状态1，第1行的消息102 'a'附近的语法不正确. Msg 102，第15级，状态1，第12行 'd'附近的语法不正确.

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

推荐答案

您所拥有的查询将给出错误

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

那是因为您在using子句中使用了*，而AnotherKey是table2和table3的一部分. 指定所需的列.另外，由于您在on条款中使用keycolumn，因此没有外部连接的用途.

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;

更新

发布实际错误总是有帮助的.

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

好像您使用的是SQL Server2005.可以从 SQL Server 2008 .

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

您可以使用select @@version检查您的SQL Server版本.

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

这篇关于在合并语句中使用联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！