Update column with unique index avoiding duplicates SQL

Question

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;

Answer 1

You need to decide what to do in the case where there are multiple values.. here is one choice

UPDATE abc AS abcTable
SET c = max( defTable.f )
FROM def as defTable
WHERE abcTable.id = abcTable.fk;