Invalid MERGE-INTO statement: Update of Primary index or partition column is not allowed

Database
Enthusiast

Invalid MERGE-INTO statement: Update of Primary index or partition column is not allowed

something new for me  - didn't use this clause in SQL before  but now need  convert to teradata: 

 

merge into tt_Pop as a
using tt_PHV as b  on a.id = b.id
when matched then
update set ID = b.id,NRW = b.NRW,DepositsSum = b.DepositsSum,
when not matched then
insert(ID,NRW,,DepositsSum,  First_DateID, Exit_DateID, ExitReason)
values(b.cd,b.NRW,b.DepositsSum,b., CAST(CAST(((CURRENT_TIMESTAMP)(FORMAT 'YYYYMMDD')) AS VARCHAR(100)) AS INTEGER),20990101,'NA');

 

it's not works - i get this  error:   Invalid MERGE-INTO statement: Update of Primary index or partition column is not allowed . 

 

any help, please!

2 REPLIES
Junior Contributor

Re: Invalid MERGE-INTO statement: Update of Primary index or partition column is not allowed

As the error message implies, Teradata doesn't allow modification of the Primary Index or partitioning column(s) within the UPDATE.

Simply remove the set ID = b.id part (which is useless anyway because both IDs are already the same).

 

 

And change this

CAST(CAST(((CURRENT_TIMESTAMP)(FORMAT 'YYYYMMDD')) AS VARCHAR(100)) AS INTEGER)

to

CAST(CURRENT_DATE AS INT) + 19000000

 

Btw, please don't post the same question in multiple forums. 

Enthusiast

Re: Invalid MERGE-INTO statement: Update of Primary index or partition column is not allowed

dnoeth  , you r the best!

 

tnx a lot!