All Forums General
terankit 77 posts Joined 03/12
29 May 2012
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

terankit 77 posts Joined 03/12
29 May 2012

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.

 

ulrich 816 posts Joined 09/09
29 May 2012

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

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

terankit 77 posts Joined 03/12
29 May 2012

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.

ulrich 816 posts Joined 09/09
29 May 2012

how about

merge into T2

using T1

on t1.a = t2.a 

     and t1.status in (1,3)

when matched then...

?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

terankit 77 posts Joined 03/12
29 May 2012

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.

ulrich 816 posts Joined 09/09
29 May 2012

you can't delete with merge.

check the manuals

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Jim Chapman 449 posts Joined 09/04
29 May 2012

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

terankit 77 posts Joined 03/12
30 May 2012

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

CarlosAL 512 posts Joined 04/08
30 May 2012

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.

 

terankit 77 posts Joined 03/12
30 May 2012

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". 

ulrich 816 posts Joined 09/09
30 May 2012

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;

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.