Share via

unable to merged multiple match column in a table

Shambhu Rai 1,411 Reputation points
2022-08-29T18:17:02.38+00:00

Create table1
( id, region string,subregion string, Date1 Date, locatiion string,value1 int,value2 int,value2 int,value3 int)
Insert into table1
values('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44),
('1','west','coastal','02-03-2022','Far East'22,34,44)

Create table2
( id, region string,subregion string, Date1 Date, locatiion string,value1 int,value2 int,value2 int,value3 int)
Insert into table2
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44),
values('1','west','coastal','04-03-2022','Far East'22,34,44)

getting error message :unable to merged multiple match column in a table when doing merge.
it should update the table in table1 with date from table 2 and also with values

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,878 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
Accepted answer
  1. LiHongMSFT-4306 25,651 Reputation points
    2022-08-30T02:43:55.07+00:00

    Hi @Shambhu Rai
    When using Merge, you need ensure one-to-one match, if there are duplicate row datas in the source table, it will result in a one-to-many match.
    How about using DISTINCT to ensure that there is no duplicate data in the source table，like this: 

    MERGE table1 T  
USING (SELECT DISTINCT * FROM table2)S ON T.id=S.id AND ......  
  
WHEN MATCHED THEN  
UPDATE SET ......  
  
WHEN NOT MATCHED BY TARGET  
THEN INSERT ......

    Best regards,
    LiHong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bjoern Peters 8,856 Reputation points
    2022-08-29T19:32:06.563+00:00

    Maybe I'm wrong, but ... if you write down your code like this and we should work with it to support you?!

    Please provide the correct code from your database with example data to provide adequate answers...

    It starts with value2 being doubled in your tables, then a comma is missing at your subregion.

  2. Tom Phillips 17,721 Reputation points
    2022-08-29T20:12:40.35+00:00

    You can't.

    MERGE requires you to have 1 match. Your sample data in table2 all matches your criteria. MERGE is protecting you from yourself, because it cannot decide which row to update with which row in table2.

    0 comments