CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle10goracle-sqldeveloper
AndreaNobili
AndreaNobili

Reputation: 42957

How can I update the value of a specific field of my table for all rows?

I am pretty new in SQL and expecially in Oracle DB and I have some doubt about how to update a specific single field values of all records of a table.

So, int an Oracle DB, I have a very simple table named PROJECT_INFO_STATUS that have the following form:

ID  STATUS
-------------
0   Closed
1   Active
2   Testing
3   Starting

As you can see in the previous snippet this table have only 2 fields: an ID (a NUMBER) and a STATUS (a VARCHAR).

What can I do to update all the rows so that the ID value is the old ID value + 1.

For example the ID value of the first row (STATUS=Closed) have to be 1 (and not 0). The ID value of the second row (**STATUS=Active) have to be 2 (and not 1). And so on.

How can I do to obtain this behavior?

Upvotes: 1

Views: 621

Answers (3)

Alex Poole
Alex Poole

Reputation: 191275

Simply set the column value to itself plus one:

update project_info_status set id = id + 1;

select * from project_info_status;

        ID STATUS    
---------- ----------
         1 Closed     
         2 Active     
         3 Testing    
         4 Started

If the ID value is used as a foreign key in another table then you would need to update all of the values in that table as well, and if it's controlled by a referential integrity constraint (as it should be) then you'd need to disable the constraint while you update all related tables, and re-enable the constraint once the data is consistent again. Unless the constraint is defined as deferrable, in which case you can update both tables in the same transaction without disabling the constraint.

But if it is a key then its actual value should be irrelevant, and you shouldn't ever really need up modify a synthetic primary key value.

Upvotes: 4

Brave Soul
Brave Soul

Reputation: 3620

update PROJECT_INFO_STATUS 
set ID = ID +1

you can directly add column value if it is not PK or FK

Upvotes: 1

WhiteHawk
WhiteHawk

Reputation: 89

you should use update command

UPDATE mytable SET new_column = <expr containing old_column>

then you can perform sum in the table query

Upvotes: 0

Related Questions