1

[PLSQL] I want to update rows in my Postgres database if the updated version wouldn't violate the primary key constraint.

Assuming the table has primary keys on col1, col2 and col3 if I run a query like this:

UPDATE table 
  SET col2='B' 
  WHERE col2='E';

'A', 'B', 'C'
'A', 'E', 'C'
'A', 'E', 'D'

The query will fail and I will get a duplicate key error because of the 2nd row.

Instead I want a query that skips the 2nd row which causes PK violation and updates the 3rd row. The required table after the update query will be like the following:

'A', 'B', 'C'
'A', 'E', 'C'
'A', 'B', 'D'
schlebe
  • 3,387
  • 5
  • 37
  • 50
Sri
  • 11
  • 1
  • 1
    PL/SQL is an Oracle product. If you are using Postgres then probably this is [Pl/pgSQL](https://postgresqltutorial.com/postgresql-plpgsql/). – William Robertson May 28 '22 at 10:23
  • By definition, the PK is the unique identifier for a record and therefore you can’t update it. Updating it would result in a different record – NickW May 28 '22 at 12:49
  • 1
    @NickW, he don't want to update rows that will make conflict with primary key, the required update query should exclude all records that may conflict with the primary key. – ahmed May 28 '22 at 12:58
  • @Ahmed you should never be updating columns that are part of a PK. You should always be doing a (delete +) insert – NickW May 28 '22 at 15:56
  • @NickW, I don't think so, it's not always the case, check this https://stackoverflow.com/questions/3838414/can-we-update-primary-key-values-of-a-table – ahmed May 29 '22 at 05:12

1 Answers1

1

The following query will exclude all rows that may conflict with the PK:

update test set col2='B'
where col2='E' and (col1,col3)  not in 
(select col1,col3 from test where col2='B');

Check the result from HERE.

ahmed
  • 9,071
  • 3
  • 9
  • 22