unable to merged multiple match column in a table

Shambhu Rai 1,406 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.
12,654 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,793 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 22,621 Reputation points Microsoft Vendor
    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
  1. Bjoern Peters 8,781 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,716 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 No comments