topic Re: Update Primary Key in Oracle table in Talend Studio

Update Primary Key in Oracle table

cd160 — Fri, 08 Mar 2019 14:47:05 GMT

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!

Re: Update Primary Key in Oracle table

akumar2301 — Fri, 08 Mar 2019 15:19:24 GMT

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;

Re: Update Primary Key in Oracle table

cd160 — Mon, 11 Mar 2019 07:26:43 GMT

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?

Re: Update Primary Key in Oracle table

akumar2301 — Mon, 11 Mar 2019 09:34:47 GMT

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.

Re: Update Primary Key in Oracle table

cd160 — Tue, 12 Mar 2019 14:06:39 GMT

Works! Thank you @uganesh !