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
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
on t1.status in (1,3)
when not matched then
Now the error is expecting something between Then and Insert.
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 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 ...