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
DennisSticklesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
Move all your conditions in the 1st MATCH condition to the JOIN condition.
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
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 
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DennisSticklesAuthor Commented:
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
0
SharathData EngineerCommented:
Glad it helped.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.