CODEWITHSUNDEEP
databasepostgresqlsql-update
Nagaraja Thangavelu
Nagaraja Thangavelu

Reputation: 1168

Updating the values of Primary key columns in Postgresql

I have the following table

|  ID | Seq No|
+++++++++++++++
| 1   | 12345 |
| 2   | 12345 |
| 3   | 12345 |
| 4   | 12345 |
| 5   | 12345 |

Both the columns are primary key values.

Here I need to increment the value by 1 for ID values greater than 2.

Following is the query I tried to execute

Update tblname
set id = id + 1
where id > 2 and seq_no = 12345

I got the following error when executing the command

ERROR: duplicate key value violates unique constraint "tblname"
DETAIL: Key (id, seq_no)=(3, 12345) already exists.

How do I solve this issue.

PS: As a workaround, I tried adding 10 to the ID value and again subtracted 9 from the ID value.

Upvotes: 1

Views: 1992

Answers (2)

Nagaraja Thangavelu
Nagaraja Thangavelu

Reputation: 1168

I found the query using WITH clause and without using any workarounds.

with target_records as (
  select id, seq_no from tblname
  where seq_no = 12345 and id > 2
  order by id desc
)
--select * from target_records;
Update tblname
Set id = target_records.id + 1
from target_records
where tblname.id = target_records.id and tblname.seq_no = 12345;

Upvotes: 1

forpas
forpas

Reputation: 164224

This workaround involves 2 update statements.
The first updates the ids to the negative values and the second makes them positive again:

Update tblname
set id = -(id + 1)
where id > 2 and seq_no = 12345;

Update tblname
set id = -id
where id < -2 and seq_no = 12345;

See the demo.
Results:

| id  | seq_no |
| --- | ------ |
| 1   | 12345  |
| 2   | 12345  |
| 4   | 12345  |
| 5   | 12345  |
| 6   | 12345  |

Upvotes: 3

Related Questions