Merge Syntax

Database

Merge Syntax

I am using a merge but I'm  having a newbie syntax error I think. 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

Tags (1)
2 REPLIES
Enthusiast

Re: Merge Syntax

Hi Craig

Try changing the SET condition to the following.

UPDATE SET
target_column1 = src.source_column1
,target_column2 = src.source_column2
,target_column3 = CURRENT_DATE
,target_column3 = 'ETL_ADHOC_USER'

Madhavi K

Enthusiast

Re: Merge Syntax

I'd suggest to CAST the 'last_update_dttm' to DATE as you are going to compare it with date(CURRENT_DATE) anyways, that should work.