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

user_1676032 0
Viewed 1145

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?

postgresql sql
1 Answers

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;
user_5931440
Related