All Forums UDA
Shehroz 1 post Joined 03/09
04 Mar 2009
Problem Using UPSERT

Hi,While trying to use UPSERT i am getting the following error" The UPDATE specified in UPSERT statement is a complex statment".below is the query that i am using. Please help me out if i am missing something here.update T1from T2 bset last_dt = b.last_dtwhere T1.msisdn = b.msisdnelseinsert into tp_tmp.sa_telenor_mshare_backupvalues ( T2.msisdn, T2.oper_cd, T2.outg, T2.incom, T2.fst_dt, T2.last_dt, T2.second_last_dt, T2.third_last_dt)Regards,Shehroz Aftab

dnoeth 4628 posts Joined 11/04
04 Mar 2009

5565 The UPDATE specified in the UPSERTstatement is a complex update.Explanation: The user specified the UPDATE in theUPSERT statement as a complex update. Examples ofcomplex update are UPDATEs that change the primaryindex value or the value of a partitioning column,UPDATEs that have subquery, and UPDATEs fromanother table. Only simple prime-key UPDATEs areallowed in UPSERTs.If you're on TD12 you might rewrite it using MERGE, is T1 ment to be an alias for tp_tmp.sa_telenor_mshare_backup?MERGE INTO tp_tmp.sa_telenor_mshare_backup AS T1USING T2 bON T1.msisdn = b.msisdnWHEN MATCHED THEN UPDATESET last_dt = b.last_dtWHEN NOT MATCHED THENINSERTVALUES ( T2.msisdn, T2.oper_cd,T2.outg, T2.incom,T2.fst_dt, T2.last_dt,T2.second_last_dt, T2.third_last_dt)Dieter

Dieter

AndreyS 2 posts Joined 08/14
29 Aug 2014

Hello, advise how to solve the problem:

UPDATE TableA

SET Name = ? 

WHERE Name = ?

ELSE 

INSERT INTO TableA (ID,Name) VALUES (?,?);

 

When running this query, I get an error - "The UPDATE specified in UPSERT statement is a complex statment"

 

I'm trying to implement logic: Insert record if not exists.

 

Advise how to fix the error, or another solution for my problem.

Raja_KT 1246 posts Joined 07/09
29 Aug 2014

My table t1 is having 
id name
1 'jj'
It works :
update t1 set name='mm' where id=2
else
insert into t1(id,name) values(2,'kk');
 
UPDATE t1 SET col_a = value_a
WHERE PI_col = PI_value ELSE
INSERT INTO t1 VALUES (PI_value,value_a);
I prefer Merge if  I have  reference table. Do you have reference table from where you can refer?

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
29 Aug 2014

This error message simply means that you're updating the PI or partitioning if the target table, which is not allowed in an UPSERT (or MERGE).

5565 The UPDATE specified in the UPSERT statement is a complex update.

Explanation: The user specified the UPDATE in the UPSERT statement as a complex update. Examples of complex update are UPDATEs that change the primary index value or the value of a partitioning column, UPDATEs that have subquery, and UPDATEs from another table. Only simple prime-key UPDATEs are allowed in UPSERTs.

Dieter

09 Sep 2014

Hi ,
I have flat file with two columns data :
id  name
-- -------
1 raj
 
 
and if i try to do
only  update will it allow ? bcz id=2 not avail in table as well.
update t1 set name= name@file where id=2 ;
 

You must sign in to leave a comment.