Postgres : multiple updates to a row by the same query is not allowed

Question

I am trying to run the below query but its throwing error ERROR: multiple updates to a row by the same query is not allowed. Can anyone pls help what I am doing wrong here.If there are duplicates in data then it should update multiple records with same value.Any suggestion what to modify in below query will be appreciated,I am new to sql not sure what i am doing wrong here

UPDATE test t1 SET 
folder=t2.folder,name=t2.name,session_name=t2.session_name,
mapping_name=t2.mapping_name,
update_date=to_char(now(),'yyyy-mm-dd hh24:mi:ss')::timestamp,is_deleted='N'
FROM stg t2 
WHERE t1.subject_id=t2.subject_id and t1.wf_id=t2.wf_id and 
t1.instance_id=t2.instance_id 
and t1.session_id=t2.session_id and t2.insert_date is null;

Answer 1

See simple example. Test data:

create table table1 (id int,val varchar(100),updated timestamp);
insert into table1 (id,val) values 
 (1,'Value 1')
,(2,'Value 2')
;
create table table2 (id int,val varchar(100));
insert into table2 (id,val) values 
 (1,'Value 1-1')
,(1,'Value 1-2')
;

Table1

Id	val	updated
1	Value 1	null
1	Value 1	null
2	Value 2	null

Table2

Id	val
1	Value 1-1

Update table1:

update table1
set val=src.val, updated=now()
from (select distinct id,val from table2) src
where src.id=table1.id;

id	val	updated
2	Value 2	null
1	Value 1-1	2024-01-26 13:31:03.61001
1	Value 1-1	2024-01-26 13:31:03.61001