New to merge statement but I want to make it work

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

SQL Server 2012 Forums

Transact-SQL (2012)

New to merge statement but I want to make it work

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2013-03-25 : 14:18:19

I am using Teradata but the merge statement does work in Teradata. I'm just having a newbie syntax error. Here is my statement:



MERGE INTO tables_dev.cust_wishlist AS tgt
USING  stage.WL_WISHLIST  AS src
	ON tgt.cust_wishlist_id = src.id
	AND tgt.cust_acct_id = src.customer_id
WHEN MATCHED THEN
	UPDATE SET
	name = cust_wishlist_name_txt,
	cust_wishlist_typ_cd = cust_wishlist_typ_cd,
	wl_private = prvt_ind,
	is_default = dflt_ind,
	ship_addr_id = ship_addr_id,
	event_dt = event_dt,
	create_date = create_dttm,
	CURRENT_DATE  = last_update_dttm,
	'ETL_ADHOC_USER' = last_update_user

It's crashing on "CURRENT_DATE = last_update_dttm,". The error message is misleading. It says, "Syntax error, expected something like a name or unicode delimited identifier or a ROWID keyword between ',' and "CURRENT_DATE".

it seems reasonable to me that an update in a merge should include the ability to insert a current date as a last_updated time stamp. BTW this syntax works fine in a regular update statement, but the merge is crashing. Help?

Craig Greenwood

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-25 : 14:43:12

Not sure about "Teradata" but for MS Sql Server the problem is you need to use square brackets around object names that are also key words. It cleaned up for me when I put brackets around the last two columns and added a semicolon to terminate the merge statement:


;MERGE INTO tables_dev.cust_wishlist AS tgt
USING  stage.WL_WISHLIST  AS src
	ON tgt.cust_wishlist_id = src.id
	AND tgt.cust_acct_id = src.customer_id
WHEN MATCHED THEN
	UPDATE SET
	name = cust_wishlist_name_txt,
	cust_wishlist_typ_cd = cust_wishlist_typ_cd,
	wl_private = prvt_ind,
	is_default = dflt_ind,
	ship_addr_id = ship_addr_id,
	event_dt = event_dt,
	create_date = create_dttm,
	[CURRENT_DATE]  = last_update_dttm,
	[ETL_ADHOC_USER] = last_update_user;

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources