programmerist 1983
asked on
How can I modify my merge data stored procedure?
Hi;
My question is related to dublicated rows. When I run my below stored procedure, It produces that error:
But; actually. I believe that there is no dublicated data if I check my tables by 2 sql query below:
QUERY 1:
Query1 and Query2 returns to me "0" rows. Query1 and Query2 have been dedecting dublicated but there is no dublication result .
My merge stored procedure: (producing above error)
My question is related to dublicated rows. When I run my below stored procedure, It produces that error:
SAP Sync -> UpdateDeliveryItems Error. Msg: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.
But; actually. I believe that there is no dublicated data if I check my tables by 2 sql query below:
QUERY 1:
select count(*),SapContractNumber, MaterialNumber, SapDeliveryNumber, Batch from DeliveryItems
GROUP by SapContractNumber, MaterialNumber, SapDeliveryNumber, Batch Having
COUNT(*) > 1
QUERY 2: ;with b as
(
SELECT d.id, d.SapContractNumber, d.MaterialNumber, d.SapDeliveryNumber, d.Batch,
row_number() over (partition by d.SapContractNumber, d.MaterialNumber, d.SapDeliveryNumber, d.Batch order by d.id desc) idx
FROM DeliveryItems d
)
select a.*
from DeliveryItems a inner join b
on a.id = b.id
where b.idx > 1
Query1 and Query2 returns to me "0" rows. Query1 and Query2 have been dedecting dublicated but there is no dublication result .
My merge stored procedure: (producing above error)
USE [xxx]
GO
/****** Object: StoredProcedure [dbo].[InsertDeliveryItemsV2] Script Date: 03/05/2017 10:24:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertDeliveryItemsV2]
AS
BEGIN
Delete from zDeliveryItems where NetPriceTotal > 0
MERGE DeliveryItems AS TARGET
USING zDeliveryItems AS SOURCE
ON (TARGET.SapContractNumber = SOURCE.SapContractNumber
AND TARGET.MaterialNumber = SOURCE.MaterialNumber
AND TARGET.SapDeliveryNumber = SOURCE.SapDeliveryNumber
AND TARGET.Batch = SOURCE.Batch)
WHEN MATCHED THEN
UPDATE
SET TARGET.[ContractDescription] = SOURCE.[ContractDescription]
,TARGET.[ExpiryDate] = SOURCE.[ExpiryDate]
,TARGET.[MaterialDescription] = SOURCE.[MaterialDescription]
,TARGET.[NetPriceTotal] = SOURCE.[NetPriceTotal]
,TARGET.[NetPriceUnit] = SOURCE.[NetPriceUnit]
,TARGET.[Position] = SOURCE.[Position]
,TARGET.[Quantity] = SOURCE.[Quantity]
,TARGET.[SapOrderPosition] = SOURCE.[SapOrderPosition]
,TARGET.[Unit] = SOURCE.[Unit]
,TARGET.[WebShopOrderNumber] = SOURCE.[WebShopOrderNumber]
WHEN NOT MATCHED THEN
INSERT ([Batch]
,[ContractDescription]
,[ExpiryDate]
,[MaterialDescription]
,[MaterialNumber]
,[NetPriceTotal]
,[NetPriceUnit]
,[Position]
,[Quantity]
,[SapContractNumber]
,[SapDeliveryNumber]
,[SapOrderPosition]
,[Unit]
,[WebShopOrderNumber])
VALUES (SOURCE.Batch, SOURCE.ContractDescription, SOURCE.ExpiryDate, SOURCE.MaterialDescription, SOURCE.MaterialNumber, SOURCE.NetPriceTotal, SOURCE.NetPriceUnit,
SOURCE.Position, SOURCE.Quantity, SOURCE.SapContractNumber, SOURCE.SapDeliveryNumber, SOURCE.SapOrderPosition, SOURCE.Unit, SOURCE.WebShopOrderNumber );
;with b as
(
SELECT d.id, d.SapContractNumber, d.MaterialNumber, d.SapDeliveryNumber, d.Batch,
row_number() over (partition by d.SapContractNumber, d.MaterialNumber, d.SapDeliveryNumber, d.Batch order by d.id desc) idx
FROM DeliveryItems d
)
delete a
from DeliveryItems a inner join b
on a.id = b.id
where b.idx > 1
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER