All Forums Tools
Waffuvan 4 posts Joined 02/12
15 Feb 2012
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!

VBurmist 96 posts Joined 12/09
15 Feb 2012

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');

 

Waffuvan 4 posts Joined 02/12
15 Feb 2012

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!

ulrich 816 posts Joined 09/09
15 Feb 2012

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Waffuvan 4 posts Joined 02/12
15 Feb 2012

How about ubstituing the CASE by IF ELSE ENDIF??

ulrich 816 posts Joined 09/09
15 Feb 2012

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. 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

asadali.khan 11 posts Joined 10/10
29 Feb 2012

You cannot use insert and update in case statement!!

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

Asad Ali Khan

You must sign in to leave a comment.