SQL Server Update values in multiple columns in table AA with values in the same columns from table B but only if condition is met

5w9g7ksd 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(101)

I need to update the information in table AA with the information from table BB but only if AA=ID4 matches BB=ID4. Below is the current table layout:

Before Update:

Table AA

ID1 ID2 ID3 ID4 ID5 ID6 ID7

10  11  12  13  0   0   0                    
20  21  22  23  0   0   0

Table BB

ID1 ID2 ID3 ID4 ID5 ID6 ID7

10  11  12  13  14  15  16                    
20  21  22  23  24  25  26

After Update:

Table AA

ID1 ID2 ID3 ID4 ID5 ID6 ID7

10  11  12  13  14  15  16                    
20  21  22  23  24  25  26

The query I built does update information but actually updates all rows not just the rows where AA=ID4 matches BB=ID4. Here is what I am using:

UPDATE AA
   Set AA.ID5 = BB.ID5,
    AA.ID6 = BB.ID6,
    AA.ID7 = BB.ID7
From AA
Inner Join BB
        ON AA.ID4 = BB.ID4

Please let me know what I am missing.

Thank you in advance

I tried the following suggestion:

SQL> merge into aa
2    using bb
3    on (aa.id4 = bb.id4
       and aa.id1 = bb.id1
       and aa.id2 = bb.id2
       and aa.id3 = bb.id3)
4    when matched then update set
5      aa.id5 = bb.id5,
6      aa.id6 = bb.id6,
7      aa.id7 = bb.id7;

But I received the following message:

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

Problem solved with the following query:

UPDATE AA
   Set AA.ID5 = BB.ID5,
       AA.ID6 = BB.ID6,
       AA.ID7 = BB.ID7
From AA
Inner Join BB
    ON AA.ID4 = BB.ID4
      AND AA.ID1 = BB.ID1
      AND AA.ID2 = BB.ID2
      AND AA.ID3 = BB.ID3

Thank you to Siggemannen for the suggestion that solved this, to Littlefoot for showing me Merge and everyone's input.

sql-server

来源：https://stackoverflow.com/questions/77159356/update-values-in-multiple-columns-in-table-aa-with-values-in-the-same-columns-fr

1条答案

按热度按时间

bmp9r5qi1#

To me, merge looks like a good choice.

Sample data; note that I modified the 2nd row in bb table so that id4 doesn't match aa.id4 :

SQL> select * From aa;

       ID1        ID2        ID3        ID4        ID5        ID6        ID7
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10         11         12         13          0          0          0
        20         21         22         23          0          0          0

SQL> select * From bb;

       ID1        ID2        ID3        ID4        ID5        ID6        ID7
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10         11         12         13         14         15         16
        20         21         22         99         24         25         26
                                         ^^
                                        here

Merge: only one row will be updated because there's only one match of aa.id4 = bb.id4 :

SQL> merge into aa
  2    using bb
  3    on (aa.id4 = bb.id4)
  4    when matched then update set
  5      aa.id5 = bb.id5,
  6      aa.id6 = bb.id6,
  7      aa.id7 = bb.id7;

1 row merged.

Result:

SQL> select * From aa;

       ID1        ID2        ID3        ID4        ID5        ID6        ID7
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10         11         12         13         14         15         16
        20         21         22         23          0          0          0

SQL>

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Update values in multiple columns in table AA with values in the same columns from table B but only if condition is met

1条答案

相关问题

热门标签

最新问答