CODEWITHSUNDEEP
sqlpostgresql
sumit
sumit

Reputation: 3740

SQL query to update a column in a composite key

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 !

Upvotes: 0

Views: 2180

Answers (2)

joop
joop

Reputation: 4513

... 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
        );

Upvotes: 2

Vao Tsun
Vao Tsun

Reputation: 51529

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

Upvotes: 1

Related Questions