Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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;
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?
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.
Works! Thank you @uganesh !