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');
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!
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.
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
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.
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
You cannot use insert and update in case statement!!
You have to use stored procedure/macros to achieve this!!
Asad Ali Khan
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!