I am trying:
merge into T2
using T1
on t1.a = t2.a
when not matched and t1.status in (1,3) then
insert
else ignore;
I am getting error that expecting something between Matched and AND.
Please help.
no additional conditions on the when not matched
and t1.status should be part of the join condition
check for example
http://forums.teradata.com/forum/general/conditional-merge-into
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
Thanks for the reply Ulrich.
Status is not the Index in the table. Can I still have that in the "ON" condition? (I have used that and it seems it is not gic=ving error).
merge into T2
using T1
on t1.status in (1,3)
when not matched then
insert
else ignore;
Now the error is expecting something between Then and Insert.
how about
merge into T2
using T1
on t1.a = t2.a
and t1.status in (1,3)
when matched then...
?
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
This is working but will not serve the requirement.
T1 is current table while T2 is History table. I need to move the row from T1 to T2 whenever status in T1 is either 1 or 3 and then delete that row from T1.
We can do this in many other ways but I wanted to know that whether we can do this using Merge Into or not.
Thanks.
you can't delete with merge.
check the manuals
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
Consider defining a trigger. I believe triggers are frequently used to capture history for audit purposes.
Thanks for the reply. I am looking for the use of Merge Into for this perticular situation. Can we use Merge Into or not? Can we use Merge Into for Delete as well in Teradata (in DB2 and Oracle we can)? Can we apply some conditions after the Match or NOT Match condition(in DB2 and in Oracke we can)?
Thanks,
Terankit
Hi:
The MERGE statement is INTENDED TO insert new records or UPDATE existing ones (as per SQL 2003 standarsd), never to DELETE records.
I don't know much about DB2, but the Oracle implementation is an extension of the standard and it can only DELETE UPDATED rows based upon the updated values.
You could try a TD multistatement request:
DELETE FROM T1 WHERE...
;INSERT INTO T2 ...
HTH.
Cheers.
Carlos.
Thanks Carlos. But can we Insert row depending upon a condition using Merge Into.
Merge Into T2
Using T1
ON T1.status in ('L1','L3')
when matched then Insert
This query is giving me error tha"expecting something between Then and Insert".
ON need to specify the join condition between T1 and T2
You need to check the manual for the merge syntax.
Why do you insist on the merge?
Did you consider
Begin Transaction;
Insert into T2
select *
from T1
where status in ('L1','L3')
;
Delete from T1
where status in ('L1','L3')
;
End Transaction;
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
Hi All,
I have two tables:T1,T2. If status in T1 is either 1 or 2 I want to insert that row in T2 and delete that row from T1. Can I do that using Merge Into or any other way to do that in one query.
Thanks,
Terankit