Solved

Using MERGE or maybe I should'nt

Posted on 2013-06-06
3
245 Views
Last Modified: 2013-06-07
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
Comment
Question by:DennisStickles
  • 2
3 Comments
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39227716
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
 

Author Comment

by:DennisStickles
ID: 39228097
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
 
LVL 40

Expert Comment

by:Sharath
ID: 39228227
Glad it helped.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now