asked on

Using MERGE or maybe I should'nt

I know the following code doesn't work, but it represents what I need to do. I'm trying to use MERGE, but it just doesn't (as far as I know) have the capability of doing what I need to do.

MERGE Production.PartType.ProductInventory AS ptpi
USING (SELECT partno,sncv, sndate, doctype, docno, detqty, snlotno, docitem,vendlotno, expirdate
FROM pcMRP.dbo.SNLOTDET
WHERE doctype = 'R' and sndate > '2013-01-01') AS MRPsld
ON MRPsld.partno = ptpi.partno
WHEN MATCHED AND (ptpi.ReceivedDate = MRPsld.sndate
			AND ptpi.ReceiverNumber = MRPsld.docno
			AND ptpi.ReceiverItemNumber = MRPsld.docitem
			AND ptpi.LotNumber = MRPsld.snlotno
			AND ptpi.ExpirationDate = MRPsld.expirdate
			AND ptpi.LocationCode != MRPsld.vendlotno)
THEN UPDATE SET ptpi.LocationCode = MRPsld.vendlotno

WHEN MATCHED AND (ptpi.ReceivedDate != MRPsld.sndate
			OR ptpi.ReceiverNumber != MRPsld.docno
			OR ptpi.ReceiverItemNumber != MRPsld.docitem
			OR ptpi.LotNumber != MRPsld.snlotno
			OR ptpi.ExpirationDate != MRPsld.expirdate)
THEN 
	INSERT (FK_ProductID,FK_LocationID, partno, LocationCode, QuantityReceived, QuantityRemaining, LotNumber, ReceiverNumber, ReceiverItemNumber, ReceivedDate, ExpirationDate)
	VALUES ((SELECT PK_ProductID FROM Production.PartType.Product WHERE partno = MRPsld.partno),10, partno, vendlotno, detqty, detqty, snlotno, docno, docitem, sndate, expirdate);

WHEN NOT MATCHED THEN
	INSERT (FK_ProductID,FK_LocationID, partno, LocationCode, QuantityReceived, QuantityRemaining, LotNumber, ReceiverNumber, ReceiverItemNumber, ReceivedDate, ExpirationDate)
	VALUES ((SELECT PK_ProductID FROM Production.PartType.Product WHERE partno = MRPsld.partno),10, partno, vendlotno, detqty, detqty, snlotno, docno, docitem, sndate, expirdate);

Open in new window

So, if I remove the second 'WHEN MATCHED..." the merge will run. But I need the logic in this area.

Here's what I'm trying to do:

The first WHEN MATCHED makes sure that all of the important fields match before checking the location. If the location doesn't match I update only that.

The second WHEN MATCHED makes sure that the part number matches and if any of the other fields don't match (not looking at the location) then insert a new row.

Lastly, if the partno's don't match (WHEN NOT MATCH) then insert a new row.

Hope this all makes sense.

Thanks, Dennis

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DennisStickles

ASKER

Wow! I guess I had been staring at this for too long. That makes perfect sense and once I added the test for the location to the first match it works like I wanted. Great job and thanks for your help!

Here is the final code:

MERGE Production.PartType.ProductInventory AS ptpi
USING (SELECT partno,sncv, sndate, doctype, docno, detqty, snlotno, docitem,vendlotno, expirdate
FROM pcMRP.dbo.SNLOTDET
WHERE doctype = 'R' and sndate > '2013-01-01') AS MRPsld
ON ptpi.partno = MRPsld.partno
AND ptpi.ReceivedDate = MRPsld.sndate
AND ptpi.ReceiverNumber = MRPsld.docno
AND ptpi.ReceiverItemNumber = MRPsld.docitem
AND ptpi.LotNumber = MRPsld.snlotno
AND ptpi.ExpirationDate = MRPsld.expirdate	
WHEN MATCHED AND ptpi.LocationCode != MRPsld.vendlotno
THEN UPDATE SET ptpi.LocationCode = MRPsld.vendlotno
WHEN NOT MATCHED THEN
	INSERT (FK_ProductID,FK_LocationID, partno, LocationCode, QuantityReceived, QuantityRemaining, LotNumber, ReceiverNumber, ReceiverItemNumber, ReceivedDate, ExpirationDate)
	VALUES ((SELECT PK_ProductID FROM Production.PartType.Product WHERE partno = MRPsld.partno),10, partno, vendlotno, detqty, detqty, snlotno, docno, docitem, sndate, expirdate);

Open in new window

-Dennis

Sharath S

Glad it helped.