SQL query to update a column in a composite key

Question

I have a table having 3 columns like this

CREATE TABLE my_table (
  A_id INTEGER NOT NULL,
  B_id INTEGER NOT NULL,
  C CHARACTER VARYING(50) NOT NULL,
  PRIMARY KEY (A_id, B_id, C)
);

I have to update some values in column B_id with other values. for which I tried this query

UPDATE my_table
SET B_id = 10
WHERE B_id = 20

But the problem is that this gives me a "duplicate key value violates unique constraint"

since at some places table has data like this

A_id, B_id, C
1,     10,  a
1,     20,  a  ## this row seems to cause constraint problem
1,     20,  b
2,     20,  a
2,     20,  b

I want the above data to become look like this

  A_id,  B_id,  C
    1,     10,  a
    1,     10,  b
    2,     10,  a
    2,     10,  b

so in general if the key combination is already present then delete the one's with value 20 otherwise update the value. Thanks in anticipation !

Possible duplicate of [how to emulate "insert ignore" and "on duplicate key update" (sql merge) with postgresql?](https://stackoverflow.com/questions/1009584/how-to-emulate-insert-ignore-and-on-duplicate-key-update-sql-merge-with-po) — Shadow, May 30 '17 at 08:37

joop · Accepted Answer · 2017-05-30T10:41:40.317

... if the key combination is already present then ignore ...

If ignore means: don't update, then you could use an exists(tuple_with _new_values in the same table), like below:

UPDATE my_table mt
SET B_id = 10
WHERE mt.B_id = 20
AND NOT EXISTS ( SELECT *
        FROM my_table nx        -- same table
        WHERE nx.A_id = mt.A_id -- same value
        AND nx.B_id = 10        -- new value
        AND nx.C_id = mt.C_id   -- same value
        );

[UPDATE] After the change in the question. You could use a CTE to combine two operations:

first: delete the records for which an update would conflict
second: update the records that were not deleted

WITH del AS ( -- delete tuples for which UPDATE would cause a conflict
        DELETE FROM my_table mt
        WHERE mt.B_id = 20
        AND EXISTS ( SELECT *
                FROM my_table nx
                WHERE nx.A_id = mt.A_id
                AND nx.B_id = 10
                AND nx.C_id = mt.C_id
                )
        RETURNING *
        )
UPDATE my_table upd -- UPDATE the records that were not deleted
SET B_id = 10
WHERE upd.B_id = 20
AND NOT EXISTS ( SELECT *
        FROM del
        WHERE del.A_id = upd.A_id
        AND del.B_id = upd.B_id
        AND del.C_id = upd.C_id
        );

Vao Tsun · Answer 2 · 2017-05-30T08:49:27.040

1

try smth like:

t=# with s as (select * from my_table where B_id = 10)
update my_table t
set B_id = 10
from s
WHERE t.B_id = 20
and s.A_id != t.A_id and s.c != t.c;

the above should update all but those that will produce exception then delete 20ties:

delete from my_table WHERE t.B_id = 20

of course both to be done in transaction at least

edited May 30 '17 at 08:49

answered May 30 '17 at 08:33

Vao Tsun

37,644
8
70
98

But all the 20's wont be replaced with 10 with this solution. the ones which are causing constraint violation should also get removed – sumit May 30 '17 at 08:39
I don't see such update statement. eithe cte deleting rows that will lead to uk duplicates and then just update, or update skipping "bad" rows and then delete. – Vao Tsun May 30 '17 at 09:18

SQL query to update a column in a composite key

2 Answers2