Update and Insert inside a CASE

Tools & Utilities
Highlighted

Update and Insert inside a CASE

I want to Update and Insert when a condition in the case was true. However, it seems that Teradata doesn't accept it. My question is, is there a way for that to happen?

Like

CASE WHEN SEL Table1.Column return TRUE

THEN UPDATE Table2 AND INSERT Table3

ELSE

INSERT Table2 AND INSERT Table3

END

I need your help asap! Thanks!

6 REPLIES
Teradata Employee

Re: Update and Insert inside a CASE

You can check the syntax of MERGE, it might be what you want.

Or just separate statements:

update table2       set ...        from table1  where         table1.column='true';

insert into table3  select ...    from table1  where         table1.column='true';

insert into table2  select ...    from table1  where NOT (table1.column='true');

insert into table3  select ...    from table1  where NOT (table1.column='true');


Re: Update and Insert inside a CASE

Here is the logic. (Not really working)

Select substr(number,1,13) as num1, substr(number,1,5) as date1 from tbl1

case

when

num1 in (select num3 from tbl3 where num3=num1)

then

seq = seq -1

else

seq='999'

end

case

when

date1 in (select date2 from tbl2)

then

update tbl2

set counter=counter+1

where date2=date1

and

insert into tbl3

values (num1, seq,counter)

else

insert into tbl2

values (date1, '0000001')

insert into tbl3

value (num1, seq, '0000001')

end

That's the actual logic, however ~ my problem is i wanted to use case and then if it returns true based on the above cases, it will then perform the update and insert or the insert and insert statements.

Thanks!

Senior Supporter

Re: Update and Insert inside a CASE

Inserts and Updates can not be used in Case statements in plain SQL - and I quess in no DB system.

So please share your table layouts and sample data which you want to processes.

Re: Update and Insert inside a CASE

How about ubstituing the CASE by IF ELSE ENDIF??

Senior Supporter

Re: Update and Insert inside a CASE

Do you want to use a stored procedure?

So far you didn't explained what you really need to do.

As VBurmist said it might be worth to check Merge syntax or check the other option. These are set operations and if need you can define them as single transactions. 

Teradata Employee

Re: Update and Insert inside a CASE

You cannot use insert and update in case statement!!

You have to use stored procedure/macros to achieve this!!