[FIXED] How do I update a value within a table that has a constraint?

Issue

Example Table: id_rel

id | other_id
-----------
1  | 123
-----------
2  | 456
-----------
3  | 123

There is a constraint on columns id, and other_id. The table is a relation table. I'd like to update all '123' values to '456' which already exist in the table. I've tried something as simple as:

UPDATE id_rel
SET other_id = 456
WHERE other_id = 123;

When I try the above I get a message like the following error:

ERROR: duplicate key value violates unique constraint "id_rel" Detail: Key (id, other_id)=(1, 456) already exists.

How can I change these values without having to remove the restraints and basically rebuild the table?

Solution

The key "456" as an unique constraint and this constraint allready exist for a another record.

You have to merge the two record or delete the one who occupy the constraint value

Answered By - Stéphane M

Answer Checked By - Pedro (PHPFixing Volunteer)