Please Tweak/Fix my MERGE statement syntax

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please Tweak/Fix my MERGE statement syntax

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2012-05-11 : 13:33:47

I'm a bit in over my head, but almost finished, so I mostly need to correct the syntax on this Merge command.

The Premise
I have several tables in my database which are price lists from different distributors. Each has a UPC [text], Price [money] & Quantity [numeric] field. I would like to create a fourth table with the aggregate. So, if each one of three list looks like Table A, I'd like the last to look like Table B.

Table A (Hypothetical distributor's inventory)
UPC		Price	Quantity
123456789	12.50	5
987654321	11.59	3
456789123	22.58	18

Table B (Hypothetical aggregate of all distributor's inventory)
UPC		Final_Price	Final_Qty	Dist1_Price	Dist1_Qty	Dist2_Price	Dist2_Qty	Dist3_Price	Dist3_Qty
123456789	$12.50		5		$10.00		22		$15.00		20		$12.50		5
987654321	$11.59		3		$12.00		0		$11.59		3		$0.00		0
456789123	$22.58		18		$0.00		0		$22.58		18		$25.00		2

The Logic
If the UPC does not match anything, INSERT Price & Qty into DistX_Price & DistX_Qty as well as Final_Price & Final_Quantity.
If the UPC matches, append it to DistX_Price & DistX_Qty
If the UPC matches AND the (QTY>0) AND (sourcePrice < targetPrice) UPDATE Final_Price & Final_Quantity

The Code (Not yet functional)

MERGE	[MyDBName].[dbo].[TestDestination] AS src1
USING	[MyDBName].[dbo].[TestSource1] AS src2 ON src2.UPC = tgt.UPC
WHEN	MATCHED AND ((tgt.Price < src2.Price) AND (tgt.Quantity < src2.Quantity))
		THEN
		UPDATE
		SET	tgt.Final_Price		= src2.Price
			tgt.Final_Quantity	= src2.Quantity
			tgt.Dist1_Price		= src2.Price
			tgt.Dist1_Quantity	= src2.Quantity

WHEN	MATCHED AND ((tgt.Price < src2.Price) OR (tgt.Quantity < src2.Quantity))
		THEN
		UPDATE
		SET	tgt.Dist1_Price		= src2.Price
			tgt.Dist1_Quantity	= src2.Quantity			

WHEN	NOT MATCHED BY TARGET
		THEN
		INSERT	(
				UPC,
				Price,
				Quantity
				)
		VALUES	(
				src2.UPC,
				src2.Dist1_Price
				src2.Dist1_Quantity
				src2.Final_Price
				src2.Final_Quantity
				);

Error Message

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'tgt'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 14:52:42

you're using wrong alias

it should be


MERGE	[MyDBName].[dbo].[TestDestination] AS src1 tgt
USING	[MyDBName].[dbo].[TestSource1] AS src2 ON src2.UPC = tgt.UPC
WHEN	MATCHED AND ((tgt.Price < src2.Price) AND (tgt.Quantity < src2.Quantity))
		THEN
		UPDATE
		SET	tgt.Final_Price		= src2.Price
			tgt.Final_Quantity	= src2.Quantity
			tgt.Dist1_Price		= src2.Price
			tgt.Dist1_Quantity	= src2.Quantity

WHEN	MATCHED AND ((tgt.Price < src2.Price) OR (tgt.Quantity < src2.Quantity))
		THEN
		UPDATE
		SET	tgt.Dist1_Price		= src2.Price
			tgt.Dist1_Quantity	= src2.Quantity			

WHEN	NOT MATCHED BY TARGET
		THEN
		INSERT	(
				UPC,
				Price,
				Quantity
                                Final_Price,
                                Final_Quantity,
              			Dist1_Price,
              			Dist1_Quantity
				)
		VALUES	(
				src2.UPC,
				src2.Final_Price,
				src2.Final_Quantity,
                                src2.Dist1_Price,
				src2.Dist1_Quantity
				);

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-12 : 06:33:05

[code] Commas were missing also

MERGE [MyDBName].[dbo].[TestDestination] AS src1 tgt
USING [MyDBName].[dbo].[TestSource1] AS src2 ON src2.UPC = tgt.UPC
WHEN MATCHED AND ((tgt.Price < src2.Price) AND (tgt.Quantity < src2.Quantity))
THEN
UPDATE
SET tgt.Final_Price = src2.Price, --commas missing
tgt.Final_Quantity = src2.Quantity,
tgt.Dist1_Price = src2.Price ,
tgt.Dist1_Quantity = src2.Quantity

WHEN MATCHED AND ((tgt.Price < src2.Price) OR (tgt.Quantity < src2.Quantity))
THEN
UPDATE
SET tgt.Dist1_Price = src2.Price, --comma missing
tgt.Dist1_Quantity = src2.Quantity

WHEN NOT MATCHED BY TARGET
THEN
INSERT (
UPC,
Price,
Quantity
Final_Price,
Final_Quantity,
Dist1_Price,
Dist1_Quantity
)
VALUES (
src2.UPC,
src2.Final_Price,
src2.Final_Quantity,
src2.Dist1_Price,
src2.Dist1_Quantity
);

[/code]

SergioM
Posting Yak Master

170 Posts

Posted - 2012-05-14 : 01:33:50

Thanks! It seems like I had several inconsistencies in the code. This thread helped me to understand the MERGE statement much better.

It now works when I only put one WHEN MATCHED criteria. However, it throws an error when more than 1 appear. If this is a known limitation, maybe I can add an If/Then statement. WHEN MATCHED THEN UPDATE (IF QTY> && targetPRICE<sourcePrice) SET Final_Price, SET Dist1_Price, ELSE SET Dist1_Price.

Does that make sense? Or should I be able to put several WHEN MATCHED commands?

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-14 : 01:35:52

Now what code have you written and what error it is throwing?

Vijay is here to learn something from you guys.

SergioM
Posting Yak Master

170 Posts

Posted - 2012-05-14 : 15:00:45

YES! I finally have semi-functional code!!! It is below. However - according to the documentation - I should be allowed (2) MATCHED statements & (2) NOT MATCHED statements. (http://technet.microsoft.com/en-us/library/bb510625.aspx).

So, if I only write (1) criteria (MATCHED or NOT MATCHED), then it works. But if I put (2) MATCHED & (1) NOT MATCHED criteria - as in the code below - it throws the following error. Line 10 refers to the second MATCH criteria. How do I get around this & use both as the documentation says I should?

quote:
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'WHEN'.

MERGE INTO [MyDBName].[dbo].[FinalList] AS targetDB
USING	[MyDBName].[dbo].[Dist1] AS sourceDB
ON sourceDB.UPC = targetDB.UPC

WHEN MATCHED AND ((targetDB.Price < sourceDB.Price) AND (sourceDB.Quantity > 0)) THEN
	UPDATE SET
	Price		= sourceDB.Price,		-- UPDATES FINAL PRICE
	Quantity	= sourceDB.Quantity;	-- UPDATES FINAL QUANTITY

WHEN MATCHED THEN
	UPDATE SET
	LastUpdate	= CURRENT_TIMESTAMP		-- UPDATES THE TIMESTAMP
	Dist1_Price	= sourceDB.Price,		-- UPDATES THE PRICE COLUMN FOR THAT DISTRIBUTOR
	Dist1_Qty	= sourceDB.Quantity;	-- UPDATES THE QUANTITY COLUMN FOR THAT DISTRIBUTOR

WHEN NOT MATCHED THEN 
	INSERT (LastUpdate, UPC, Manufacturer, Model, Title, Price, Quantity, Dist1_Price, Dist1_Qty)
	VALUES (CURRENT_TIMESTAMP, sourceDB.UPC, sourceDB.Manufacturer, sourceDB.Model, sourceDB.Title,  sourceDB.Price, sourceDB.Quantity, sourceDB.Price, sourceDB.Quantity);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-14 : 15:10:53

You have to remove the semi-colons.

sourceDB.Quantity; <---	-- UPDATES FINAL QUANTITY

The entire merge including the WHEN MATCHED and WHEN NOT MATCHED are all one statement, but the semi-colon is an end-of-statement marker. Hence the error.

In addition, of course, you have to combine the two WHEN MATCHED blocks into one - you can use case expressions to update only the parts you want.

SergioM
Posting Yak Master

170 Posts

Posted - 2012-05-14 : 16:08:17

Now we're talking! One step closer. Now I have a new (new) hurdle. It looks like I can use MATCHED twice, but not for UPDATE twice. MATCH may only use UPDATE once.

quote:
An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.

Now my code looks like this. It has (1) MATCHED clause & an If Statement within it. But it still seems to be wrong! Any thoughts?

MERGE INTO [MyDBName].[dbo].[FinalList] AS targetDB
USING	[MyDBName].[dbo].[Dist1] AS sourceDB
ON sourceDB.UPC = targetDB.UPC

WHEN MATCHED THEN
    UPDATE SET
	LastUpdate	= CURRENT_TIMESTAMP,			-- UPDATES THE TIMESTAMP
	Dist1_Price	= sourceDB.Price,			-- UPDATES THE PRICE COLUMN FOR THAT DISTRIBUTOR
	Dist1_Qty	= sourceDB.Quantity,			-- UPDATES THE QUANTITY COLUMN FOR THAT DISTRIBUTOR
	IF ((targetDB.Price < sourceDB.Price) AND (sourceDB.Quantity > 0))
	UPDATE SET
	Price		= sourceDB.Price,			-- UPDATES FINAL PRICE
	Quantity	= sourceDB.Quantity,			-- UPDATES FINAL QUANTITY

WHEN NOT MATCHED THEN 
      INSERT (LastUpdate, UPC, Manufacturer, Model, Title, Price, Quantity, Dist1_Price, Dist1_Qty)
      VALUES (CURRENT_TIMESTAMP, sourceDB.UPC, sourceDB.Manufacturer, sourceDB.Model, sourceDB.Title,  sourceDB.Price, sourceDB.Quantity, sourceDB.Price, sourceDB.Quantity);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-14 : 17:39:16

quote:
Originally posted by SergioM

Now we're talking! One step closer. Now I have a new (new) hurdle. It looks like I can use MATCHED twice, but not for UPDATE twice. MATCH may only use UPDATE once.

quote:
An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.

Now my code looks like this. It has (1) MATCHED clause & an If Statement within it. But it still seems to be wrong! Any thoughts?

MERGE INTO [MyDBName].[dbo].[FinalList] AS targetDB
USING [MyDBName].[dbo].[Dist1] AS sourceDB
ON sourceDB.UPC = targetDB.UPC

WHEN MATCHED THEN
UPDATE SET
LastUpdate = CURRENT_TIMESTAMP, -- UPDATES THE TIMESTAMP
Dist1_Price = sourceDB.Price, -- UPDATES THE PRICE COLUMN FOR THAT DISTRIBUTOR
Dist1_Qty = sourceDB.Quantity, -- UPDATES THE QUANTITY COLUMN FOR THAT DISTRIBUTOR
IF ((targetDB.Price < sourceDB.Price) AND (sourceDB.Quantity > 0))
UPDATE SET
Price = sourceDB.Price, -- UPDATES FINAL PRICE
Quantity = sourceDB.Quantity, -- UPDATES FINAL QUANTITY

WHEN NOT MATCHED THEN
INSERT (LastUpdate, UPC, Manufacturer, Model, Title, Price, Quantity, Dist1_Price, Dist1_Qty)
VALUES (CURRENT_TIMESTAMP, sourceDB.UPC, sourceDB.Manufacturer, sourceDB.Model, sourceDB.Title, sourceDB.Price, sourceDB.Quantity, sourceDB.Price, sourceDB.Quantity);

T-SQL syntax does not let you use IF clause as you have used there. You can use CASE expressions - like this:

WHEN MATCHED THEN
	UPDATE SET
		LastUpdate	= CURRENT_TIMESTAMP,			-- UPDATES THE TIMESTAMP
		Dist1_Price	= sourceDB.Price,			-- UPDATES THE PRICE COLUMN FOR THAT DISTRIBUTOR
		Dist1_Qty	= sourceDB.Quantity,			-- UPDATES THE QUANTITY COLUMN FOR THAT DISTRIBUTOR
		Price = 
		CASE 
			WHEN (targetDB.Price < sourceDB.Price) AND (sourceDB.Quantity > 0) THEN sourceDB.Price
			ELSE targetDB.Price
		END,
		Quantity = 
		CASE
			WHEN (targetDB.Price < sourceDB.Price) AND (sourceDB.Quantity > 0) THEN sourceDB.Quantity
			ELSE targetDB.Quantity
		END

SergioM
Posting Yak Master

170 Posts

Posted - 2012-05-14 : 18:13:23

BINGO! You guys are awesome! This took hours of reading & testing, but it was WELL worth it!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-14 : 19:32:19

Glad you got it working!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources