Postgresql update multiple rows and skip row on error I have a query which updates single column of multiple records using column value from another table. For example below are the tables--

Table A

+-----------------+---------------------+------+
| id(primary_key) | user_id(unique_key) | f_id |
+-----------------+---------------------+------+
| 1               | 1                   | 11   |
+-----------------+---------------------+------+
| 2               | 3                   | 22   |
+-----------------+---------------------+------+


Table B

+----+------+
| id | f_id |
+----+------+
| 1  | 11   |
+----+------+
| 3  | 22   |
+----+------+


Now I tried to update user_id column for all records in Table A using id column from Table B on basis of f_id column which is common using below query.

UPDATE 
    A
SET 
    user_id = B.id
FROM 
    B
WHERE A.f_id = B.f_id


user_id column in Table A is unique which generates an error on bulk update as:


  ERROR: duplicate key value violates unique constraint "A_user_id_key"
  DETAIL: Key (user_id)=(221207) already exists.


But when I update this particular record using below query 

UPDATE 
    A
SET 
    user_id = 221207
FROM 
    B
WHERE A.f_id = B.f_id


it executes successfully without any error. Only bulk updates shows that error.
I don't get what is going wrong.
It seems there is a UNIQUE INDEX on table A(user_id) and you're trying to assign an existing value to more than one row.

Have a look at the next example:

create table a (id int, user_id int, f_id int);
create table b (id int, f_id int);
create unique index A_user_id_key on a(user_id);

insert into a 
values (1, 1, 11),
       (2, 3, 22),
       (3, 2, 33),
       (4, 5, 44 );

insert into b
values (1, 11),
       (3, 22),
       (3, 33), --&lt;&lt;&lt;&lt; there is another row with user_id = 3
       (4, 44);


When I try to update using your current query:

update a
set    user_id = b.id
from   b
where  a.f_id = b.f_id;


It returns same error message:


  ERROR:  duplicate key value violates unique constraint "a_user_id_key"
  DETAIL:  Key (user_id)=(3) already exists.


You could solve by avoiding existing values:

update a
set    user_id = b.id
from   b
where  a.f_id = b.f_id
and    not exists(select 1 from a where a.user_id = b.id);


This is the result:




select * from a;



id | user_id | f_id
-: | ------: | ---:
 1 |       1 |   11
 2 |       3 |   22
 3 |       2 |   33
 4 |       4 |   44



db&lt;>fiddle here

But I'll suggest to check wich are the duplicate rows using next query:




select a.*
from   a
join   b
on     a.f_id = b.f_id
and    exists(select 1 from a where user_id = b.id and f_id &lt;&gt; b.f_id);



id | user_id | f_id
-: | ------: | ---:
 3 |       2 |   33



db&lt;>fiddle here