I have 2 tables where I need to update one column with the records from the other table:
create table abc(id,b,c)as values
('ab1', 1, null)
,('ab2', 2, null)
,('ab3', 3, null)
,('ab4', 4, null) ;
alter table abc
add constraint abc_c_uniq unique(c)
,add constraint abc_pk primary key(id);
create table def(id,e,f,fk) as values
(1, 1, 'text1', 'ab1')
,(2, 2, 'text2', 'ab2')
,(3, 3, 'text3', 'ab3')
,(4, 3, 'text3', 'ab4') ;
alter table def
add constraint def_pk primary key(id)
,add constraint def_fk_fk foreign key (fk) references abc(id);
I need to update the values of column c
in table abc
with values from f
from table def
. Column c
has a unique constraint
and there is duplicated values for column f
in table def
but with different foreign keys
, when I try to run my query I got an error:
ERROR: duplicate key value violates unique constraint "abc_c_uniq" DETAIL: Key (c)=(text3) already exists.
Here is my query:
UPDATE abc
SET c = def.f
FROM def
WHERE abc.id = def.fk;
3
Answers
You need to decide what to do in the case where there are multiple values.. here is one choice
NB: Gently reminder before you run these code make sure you begin a
transaction
if it is a livedb
So , I am more concerned with duplicates which
Randy
alluded too. How I went solving this would solely be on things I have done in the past.Having said that, given that there might be multiple values for
f
indef
.We can gently use it to
update
by choosing the minimum value offk
if duplicates occurs. This will ensure that you can updateabc.c
with the smallestf
value for eachfk
A code snippet to guide you:
You can use a
distinct on
to pick only onedef.fk
to be used: demo at db<>fiddleOtherwise, as the error suggests, you end up trying to use the two
text3
‘s fromdef
for two different rows inabc
, violating the unique constraint.