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.
I am trying:
merge into T2
on t1.a = t2.a
when not matched and t1.status in (1,3) then
I am getting error that expecting something between Matched and AND.
no additional conditions on the when not matched
and t1.status should be part of the join condition
check for example
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).
on t1.status in (1,3)
when not matched then
Now the error is expecting something between Then and Insert.
on t1.a = t2.a
and t1.status in (1,3)
when matched then...
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.
you can't delete with merge.
check the manuals
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)?
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 ...
Thanks Carlos. But can we Insert row depending upon a condition using Merge Into.
Merge Into T2
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
Insert into T2
where status in ('L1','L3')
Delete from T1