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.
You are viewing: When I D
I have received an update:
Read more : When Is The Best Time To Prune A Maple Tree
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:
Read more : When Is It Too Late To Get Dental Implants
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];
Source: https://t-tees.com
Category: WHEN
