Archives of the TeradataForum

Message Posted: Tue, 11 Jan 2011 @ 10:26:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Have you tried UPSERT (an extended form of the UPDATE command)

Below is the description i got.. Not sure if it reach your requirement

The logic of an Upsert operation is as follows:

1. Attempt to update a row based on its Primary Index value.

2. If the update fails because the row doesn't exist, insert the row.

Assume a table 'table1' exists as follows.

     CREATE TABLE table1 (PI_col INT, col_a INT) UNIQUE PRIMARY INDEX (PI_col);

The simplest syntax of the Upsert form of the UPDATE command is as follows:

     UPDATE table1 SET col_a = value_a
     WHERE PI_col = PI_value ELSE
     INSERT INTO table1 VALUES (PI_value,value_a);

There are some key rules governing the use of this form:

* The INSERT and UPDATE must reference the same single row of the same table.

* The UPDATE must use the Primary Index of the table.

* The target table may be a table or a view.

In order to use the 'Upsert' form of UPDATE, it is necessary for the user to have both of the following permissions:

* INSERT privilege on table1 (or view1)

* UPDATE privilege on table1 (or view1)

Thanks

N.Machha

	<Prev	Next>		<<First	<Prev	Next>	Last>>