How to use MERGE for Updating records when ID is repeating?

Question

Hi everyone,

I have a SQL table name called: Address
In this table I have 4 columns: Add_ID, Address, City and a foreign key UniqueID.

I have received an update:

And I am trying to update my table using Merge SQL statement but I am getting error because UniqueID is repeating.

Msg 8672, Level 16, State 1, Line 3  
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.  
  
Completion time: 2022-08-02T14:47:21.5433331+05:00  
  

Below is my code:

MERGE Address AS Add_Target  
USING Address_TEMP AS AddTEMP_Source  
ON AddTEMP_Source.Address_Record_ID_FK = Add_Target.Address_Record_ID_FK  
 
  
--For INSERT  
WHEN NOT MATCHED BY Target THEN  
 INSERT (Address_Line_1,Address_Record_ID_FK)  
 VALUES(AddTEMP_Source.Address_Line_1,AddTEMP_Source.Address_Record_ID_FK)  
  
--For UPDATE  
WHEN MATCHED THEN UPDATE SET  
 Add_Target.Address_Line_1 = AddTEMP_Source.Address_Line_1,  
 Add_Target.Address_Record_ID_FK = AddTEMP_Source.Address_Record_ID_FK;  

--TABLE INFORMATION:
--Individual (MAIN TABLE)

CREATE TABLE [FPM_COMPLIANCE_DATA_UDPATE_20220722].[dbo].[Individual]  
(   
[UniqueID] int identity(1000000,1),  
Record_ID nvarchar(4000) unique not null,  
--[UniqueID] nvarchar(20) not null,   
--Record_ID int identity(1,1),  
Record_Type nvarchar(4000) null,  
[Title] nvarchar(4000)  null ,  
[Alternate_Title] nvarchar(4000)  null ,  
[Forename] nvarchar(1300)  null ,   
[Middlename] nvarchar(1300)  null ,  
[Surname] nvarchar(1400)  null ,   
[Full_Name] nvarchar(4000)  null ,   
[Gender] nvarchar(4000)  null ,   
[Nationality] nvarchar(4000)  null ,   
[Source] nvarchar(4000)  null ,   
[Date_of_Capture] DATETIME null ,  
[Soft_Delete] nvarchar(4000)  null ,   
[Date_of_Soft_Delete] DATETIME  null ,   
[Catefor xml path ry] nvarchar(4000)  null ,  
[Sub_Catefor xml path ry] nvarchar(4000)  null ,  
--[Picture] nvarchar(4000)  null,   
--[Sanctions_Data] nvarchar(4000) NULL,   
Listed_On dateTIME,  
Modified_On dateTIME,  
Individual_List_ID_FK int foreign key references List(List_ID)  
ON UPDATE CASCADE  
ON DELETE CASCADE,  
File_Names nvarchar(4000),  
--primary key (Record_ID),  
primary key (UniqueID)  
) ;  
  
ALTER TABLE [FPM_COMPLIANCE_DATA_UDPATE_20220722].[dbo].[Individual] CHECK CONSTRAINT ALL  
  
--Address Table  
  
CREATE TABLE [FPM_COMPLIANCE_DATA_UDPATE_20220722].[dbo].[Address]  
( Add_Id int identity(1,1) ,  
[Address_Line_1] nvarchar(4000)  null ,   
[Address_Line_2] nvarchar(4000)  null ,   
[Address_Line_3] nvarchar(4000)  null ,   
[Address_Line_4] nvarchar(4000)  null ,   
[Town_District] nvarchar(4000)  null ,  
[City] nvarchar(4000)  null ,  
[County_State] nvarchar(4000)  null ,   
[Post_Zip_Code] nvarchar(4000)  null ,  
[Country] nvarchar(4000)  null ,   
[ISO_Country] nvarchar(4000)  null ,   
[Soft_Delete_Address] nvarchar(4000)  null ,  
[Address_Record_ID_FK] nvarchar(4000) null ,--foreign key references Individual(Record_ID),  
[Address_UniqueID_FK] int null foreign key references Individual(UniqueID)  
ON UPDATE CASCADE  
ON DELETE CASCADE,  
File_Names nvarchar(4000),  
primary key (Add_Id)  
) ;  
ALTER TABLE [FPM_COMPLIANCE_DATA_UDPATE_20220722].[dbo].[Address] CHECK CONSTRAINT ALL  
  
  
--Address_TEMP staging table  
  
--FOR ADDRESS_TEMP  
IF EXISTS   
(SELECT * FROM DBO.SYSOBJECTS   
WHERE ID = OBJECT_ID(N'[FPM_COMPLIANCE_DATA].[dbo].[Address_TEMP]')   
--AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1  
)   
BEGIN   
ALTER TABLE [FPM_COMPLIANCE_DATA].[dbo].[Address_TEMP] NOCHECK CONSTRAINT ALL  
DROP TABLE [FPM_COMPLIANCE_DATA].[dbo].[Address_TEMP]END ;  
  
SELECT TOP(0) * INTO [Address_TEMP] FROM [Address];  
  

Answer 1

It is not clear why are you using an UniqueID which is not unique and why did you show a code that deals with different columns.

For described tables, try this statement:

merge [Address] a   
using Address_TEMP t on a.Add_ID = t.Add_ID  
when matched then  
    update   
    set [Address] = t.[Address],  
        [City] = t.[City]  
when not matched then  
    insert (Add_ID, [Address], City)  
    values (t.Add_ID, t.[Address], t.City)  
;  

Answer 2

Before we start to write statements, let's first get an understanding of what you want to do and not the least the logic.

Taking what you have in the screenshot, it is all a guessing game. There are two rows for ID 1001 in the source and two rows in target. By which rules do you want to map these rows? If you don't know, you can't expect MERGE to know?

With the sample data, we can guess that Add_ID = 1 goes to Add_ID = 390, because there is a match on the Address field. But we can see from the Add_ID = 2 and Add_ID = 391 that we cannot use this as a rule, since the Address may change.

So you need to determine the logic by which update is to take place. I put you in emphasis, since no one else here knows about your business. Once you have the logic encoded in English, we can help you with transforming into SQL.

Answer 3

MERGE does not allow you to operate on the same record more than 1 time. So you must dedup your source data before you run MERGE.

You didn't really give us enough information to help you with your problem. But you need so do something like:

MERGE Address AS Add_Target  
 USING (  
	SELECT *  
	FROM (  
		SELECT *, ROW_NUMBER() OVER (PARTITION BY Address_Record_ID_FK) as rn  FROM Address_TEMP  
	)a  
	WHERE a.rn = 1  
	) AS AddTEMP_Source  
 ON AddTEMP_Source.Address_Record_ID_FK = Add_Target.Address_Record_ID_FK  
...  
  

Answer 4

Hi,@him tim

The case you posted doesn't match the code , which makes it more difficult for us to understand your needs . After going through the link you posted yesterday , I'm probably guessing your state at this point : you need a temporary table to receive the daily generated code , but There are many tables , some of which need to be updated if the data matches , and some of which need to be added , and are finally aggregated into that temporary table .

As shown in the picture , you have duplicate IDs . I guess this is the final result table . The address and city here are updated at the same time . Although this is in line with your own logic , it is not so smart for SQL . It's not entirely clear how he fits .

Tom's opinion is very important, you need to change the source to the result after deduplication in order to use merge correctly .

For the problem of deduplication of merge, please browse this link. The idea is the same . The analysis function is used . I hope you will solve the problem of id duplication in your spare time.

Bert Zhou