Update Primary Key in Oracle table

[cd160]

How can I update Primary Key value in Oracle table using Talend?

I need to increment the values of the Primary Key by 1 starting from the highest value backwards, so that I don't get duplicates.

Example:

Key--New Value

1--2

2--3

3--4

4--5

Thanks!

[akumar2301]

Assuming your primary is not Sequence  and Your Primary is not Foreign Key of others ,

As per my suggestion ,You should  disable the Primary Constraints ,

ALTER TABLE table_name
DISABLE CONSTRAINT primary_constraint_name;

Update Primary  Column  

Update table_name set pcol = pcol +1 ;

Commit;

enable Primary Key constraints and Analyse it so that Primary Key Index are re-Build correctly.

ALTER TABLE table_name
ENABLE CONSTRAINT primary_constraint_name;

ANALYZE TABLE table_name VALIDATE STRUCTURE CASCADE FAST;

[cd160]

Thank you @uganesh for the reply.

I know we can do this using the query below, but I was kind of wondering if there was another way around it in Talend, using less 'written' commands.

Here is my dilemma:

Table has two columns (Key1, Key2) that go into Primary Key and together create the Unique constraint. Key1 is set as Foreign key for another table.

Key1  Key2

A1  1

A1  2

A1  3

A1  4

A2  1

A2  2

A3  1

A3  2

A3  3

I want to increment the values of Key2 by 1 so that the value "1" is available again, because my next step is to insert a new row in my Table with Key2 value "1".

A1  1 (new inserted row)

A1  2 (old rows with Key2 value incremented)

A1  3

A1  4

A1  5

There is no way around using the query I guess?

[akumar2301 · Accepted solution]

in this case , TOracleRow ,

"begin

update table1 SET Key2= Key2 + 1  WHERE Key1 = 'A1' ;
Commit ;

end;

"

Should work.  Then you could insert new record with Key2 as 1.

Note : If you need to do it in data flow , better to create a Oracle Trigger.

[cd160]

Works! Thank you @uganesh !