Insert and Delete in Merge Into

General
Enthusiast

Insert and Delete in Merge Into

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

11 REPLIES
Enthusiast

Re: Insert and Delete in Merge Into

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.

Senior Supporter

Re: Insert and Delete in Merge Into

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

Enthusiast

Re: Insert and Delete in Merge Into

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.

Senior Supporter

Re: Insert and Delete in Merge Into

how about

merge into T2

using T1

on t1.a = t2.a 

     and t1.status in (1,3)

when matched then...

?

Enthusiast

Re: Insert and Delete in Merge Into

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.

Senior Supporter

Re: Insert and Delete in Merge Into

you can't delete with merge.

check the manuals

Enthusiast

Re: Insert and Delete in Merge Into

Consider defining a trigger.  I believe triggers are frequently used to capture history for audit purposes.

Enthusiast

Re: Insert and Delete in Merge Into

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

Junior Supporter

Re: Insert and Delete in Merge Into

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.