1

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.

  • Please add table schema of tables A and B. – McNets Jun 6 '18 at 17:28
  • I have created a minimal question to reproduce the error whole table has many columns but problem is when updating user_id column – Himanshu Jun 6 '18 at 17:46
3

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), --<<<< 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<>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 <> b.f_id);
id | user_id | f_id
-: | ------: | ---:
 3 |       2 |   33

db<>fiddle here

  • Thanks for your effort I will compare this with my original schema. – Himanshu Jun 6 '18 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.