Problem Using UPSERT

[Shehroz]

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
T1
from T2 b
set last_dt = b.last_dt
where T1.msisdn = b.msisdn
else
insert into tp_tmp.sa_telenor_mshare_backup
values ( 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]

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.

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 T1
USING T2 b
ON T1.msisdn = b.msisdn
WHEN MATCHED THEN
UPDATE
SET last_dt = b.last_dt
WHEN NOT MATCHED THEN
INSERT
VALUES ( T2.msisdn, T2.oper_cd,
T2.outg, T2.incom,
T2.fst_dt, T2.last_dt,
T2.second_last_dt, T2.third_last_dt)

Dieter

[AndreyS]

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]

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?

[dnoeth]

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.

[reddyrajendra20]

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 ;