Link to home
Start Free TrialLog in
Avatar of programmerist 1983
programmerist 1983Flag for Türkiye

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:

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.

Open in new window


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

Open in new window

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

Open in new window


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  

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of programmerist 1983

ASKER

perfect!!!!