Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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
0
DennisStickles
Asked:
DennisStickles
  • 2
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now