SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 New to merge statement but I want to make it work
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

craigwg
Posting Yak Master

USA
154 Posts

Posted - 03/25/2013 :  14:18:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/25/2013 :  14:43:12  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000