SQL Merge Error : The MERGE statement attempted to UPDATE or DELETE
You are joining the tables on ON source.MappingId = target.MappingId
.
In your data sample, there are more than 1 row with same MappingId = 185761
.
So here you got:
A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times.
You need to specify some unique column combination to join the source
and the target
tables.
MERGE attempted to UPDATE or DELETE the same row more than once
For the last update only use;
INSERT INTO SANDBOX.EHN.TARGET_SCD2
SELECT BUSINESS_KEY
,DESCRIPTION_A
,M_CRC
,StartDATE
,EndDATE
FROM (
MERGE SANDBOX.EHN.TARGET_SCD2 D
USING SANDBOX.EHN.SOURCE_SCD2 UPD
ON(D.BUSINESS_KEY = UPD.BUSINESS_KEY AND UPD.EndDATE = '9999-12-31')
WHEN MATCHED AND D.EndDATE = '9999-12-31'
THEN UPDATE SET D.EndDATE = UPD.StartDATE
OUTPUT $Action Action_Out, UPD.BUSINESS_KEY
, UPD.DESCRIPTION_A
, UPD.M_CRC
, UPD.StartDATE
, UPD.EndDATE
)AS MERGE_OUT
WHERE MERGE_OUT.Action_Out = 'UPDATE'
If you want ALL SRC rows in your target table then I agree with Nick.McDermaid
For ALL rows use;
UPDATE TRG
SET TRG.EndDate = SRC.StartDATE
FROM SANDBOX.EHN.TARGET_SCD2 TRG
JOIN ( select SRC.BUSINESS_KEY, min(src.StartDATE)StartDATE
from SANDBOX.EHN.SOURCE_SCD2 SRC
group by SRC.BUSINESS_KEY
)SRC
on ( TRG.BUSINESS_KEY = SRC.BUSINESS_KEY
AND SRC.StartDate > TRG.StartDate )
where 1 = 1
INSERT SANDBOX.EHN.TARGET_SCD2
SELECT * FROM SANDBOX.EHN.SOURCE_SCD2
IF EXISTS and MERGE Statement
Seems you have duplicate rows in your target table which are loaded from your previous runs.
Note: Matching in a Merge does not consider the rows inserted (even duplicate) while running the Merge itself.
Below is my repro example with a sample data:
Table1: Initial data
Table2: Taget table
Merge Statement:
MERGE tb2 AS Target
USING tb1 AS Source
ON Source.firstname = Target.firstname and
Source.lastname = Target.lastname
-- For Inserts
WHEN NOT MATCHED BY Target THEN
INSERT (firstname, lastname, updated_date)
VALUES (Source.firstname, Source.lastname, source.updated_date)
-- For Updates
WHEN MATCHED THEN UPDATE SET
Target.updated_date = Source.updated_date
-- For Deletes
WHEN NOT MATCHED BY Source THEN
DELETE;
When Merge is executed, it inserts all data without any errors.
New data in tb1:
When I run the Merge statement, it gives me the same error as yours.
As a workaround using one of the below options,
Add additional conditions if possible in the ON clause to uniquely identify the data.
Remove the duplicates from the source and merge the data into tb2 as below.
--temp table
drop table if exists #tb1;
select * into #tb1 from (
select *, row_number() over(partition by firstname, lastname order by firstname, lastname, updated_date desc) as rn from tb1) a
where rn = 1
MERGE tb2 AS Target
USING #tb1 AS Source
ON Source.firstname = Target.firstname and
Source.lastname = Target.lastname
-- For Inserts
WHEN NOT MATCHED BY Target THEN
INSERT (firstname, lastname, updated_date)
VALUES (Source.firstname, Source.lastname, source.updated_date)
-- For Updates
WHEN MATCHED THEN UPDATE SET
Target.updated_date = Source.updated_date
-- For Deletes
WHEN NOT MATCHED BY Source THEN
DELETE;
Data merged into tb2 successfully.
Getting SQL Server Merge Error
Merge
seems unnecessary for this logic. Something like:
UPDATE ProductRankingHistory P
SET PRHAverageRating = (SELECT AVG(r.ReviewRateValue)
FROM Review r
WHERE r.ProductID = P.PRHProduct AND YEAR(r.ReviewRateDate) = P.PRHYear
)
WHERE EXISTS (SELECT 1
FROM Review r
WHERE r.ProductID = P.PRHProduct AND YEAR(r.ReviewRateDate) = P.PRHYear
);
After writing this, I see that you are probably using SQL Server (or a similar database). The following might work:
UPDATE p
SET PRHAverageRating = rp.avg_ReviewRateValue
FROM ProductRankingHistory p JOIN
(SELECT r.ProductID, YEAR(r.ReviewRateDate) as yyyy, AVG(r.ReviewRateValue) as avg_ReviewRateValue
FROM Review r
GROUP BY r.ProductID, YEAR(r.ReviewRateDate)
) rp
ON rp.ProductId = p.PRHProduct AND rp.yyyy = p.prhYear;
MERGE statement in stored procedure throwing duplicate rows error
Removing complete duplicate rows from the DataTable is not enough. You must ensure there is only one row in the DataTable for the match condition.
ON Target.ItemUPC = Source.ItemUPC
AND Target.Store = Source.Store
AND Target.RoutingEffectiveDate = Source.RoutingEffectiveDate
You can do this in SQL Server by merging from a CTE that dedups the input, something like:
with q as
(
select *, row_number() over (partition by ItemUPC, Store order by RoutingEffectiveDate desc) rn
from @UpdateRecords
), q2 as
(
select * from q where rn = 1
)
MERGE INTO Imp_Master_DSDCostPrice AS Target
USING q2 AS Source . . .
Issues with SQL Server MERGE statement
Any of the four values in #S
will match your target table's single row value (all values in #S have id = 1 and name = 'A' - so they all match the single row in the target), thus this value will be updated four times - that's what the error says, and it's absolutely right.
What is it you really want to achieve here??
Do you want to set the Address to the first of the values from the source table? Use a TOP 1
clause in your subselect:
MERGE #T
USING (SELECT TOP 1 id, name, address FROM #S) AS S
ON #T.id = S.id AND #T.Name = S.Name
WHEN NOT MATCHED THEN
INSERT VALUES(S.id,S.Name, S.Address)
WHEN MATCHED THEN
UPDATE SET Address = S.Address;
Do you want to set the Address to a random element of the values from the source table? Use a TOP 1
and ORDER BY NEWID()
clause in your subselect:
MERGE #T
USING (SELECT TOP 1 id, name, address FROM #S ORDER BY NEWID()) AS S
ON #T.id = S.id AND #T.Name = S.Name
WHEN NOT MATCHED THEN
INSERT VALUES(S.id,S.Name, S.Address)
WHEN MATCHED THEN
UPDATE SET Address = S.Address;
If you match four source rows to a single target row, you'll never get a useful result - you need to know what you really want.
Marc
Related Topics
Find Top 10 Latest Record for Each Buyer_Id for Yesterday's Date
Does Except Execute Faster Than a Join When the Table Columns Are the Same
Split String in SQL Server to a Maximum Length, Returning Each as a Row
Best Practices for the Order of Joined Columns in a SQL Join
Limiting Returned Record from SQL Query in Oracle
Listing Files in a Specified Directory Using Pl/Sql
A Constraint That Only Allows One of Two Tables to Reference a Base Table
Mssql: Update Statement Avoiding the Check Constraint
Postgresql Count Number of Times Substring Occurs in Text
What Is the Default SQL Result Sort Order with 'Select *'
Sql: Performance Comparison for Exclusion (Join VS Not In)
How to Use in Clause with Preparedstatement in Postgresql