Postgres update column only if column of same value does not exist

Question

I need to do a simple update operation on some columns in a join table on my DB, however, I do not want to make the update if a column with the updated value already exists.

So say the join table looks something like this:

_id | fkId
----------
1   | A
1   | B
2   | B
3   | C
3   | B
4   | A

I want to update all the entries that have the fkId of B, to A
However, each entry must be unique, so if I already have another entry for that same _id already set to fkId A, then I don't want to do the update but instead just get rid of it

My update currently looks like the following:

  UPDATE my_table
  SET "fkId"='A'
  WHERE "fkId"='B';

In my example table above you see entry of _id 1, so if I run this query I will end up with two entries as

_id | fkId
----------
1   | A
1   | A

I do not want this. Each pair needs to be unique, so it must be deleted. How can I have this happen through a query?

Abelisto · Accepted Answer · 2020-04-03 21:24:42Z

2

Use not exists condition additionally:

UPDATE my_table AS t
  SET "fkId"='A'
  WHERE "fkId"='B' AND NOT EXISTS (
    SELECT 1 from my_table WHERE _id=t._id AND "fkId" = 'A');

Then, if you want to delete rows that not updated - just do it:

DELETE FROM my_table WHERE "fkId" = 'B';

To avoid interventions from parallel sessions between those two queries you probably need to lock rows before:

SELECT * FROM my_table WHERE "fkId" = 'B' FOR UPDATE;

So your whole statements sequence could be:

BEGIN;
SELECT * FROM my_table WHERE "fkId" = 'B' FOR UPDATE;
UPDATE my_table AS t
  SET "fkId"='A'
  WHERE "fkId"='B' AND NOT EXISTS (
    SELECT 1 from my_table WHERE _id=t._id AND "fkId" = 'A');
DELETE FROM my_table WHERE "fkId" = 'B';
COMMIT;

answered Apr 3, 2020 at 21:24

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres update column only if column of same value does not exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related