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

Question

I have staging and main table. Using staging table I am updating main table Below are inputs and expected output for reference and I have written query but not sure why its not updating duplicate records and throwing error

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

Below example is just for illustration purposes. I gave actual data on this fiddle.

stg

Id	val	updated
1	Value 1	null
2	Value 2	null
3	Value 3	null
3	Value 3	null
4	Value 4	null

main

Id	val	updated
1	Value 5	null
1	Value 5	null
2	Value 8	null
2	Value 8	null
3	Value 3	null

Expected Output :

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

I have tried below query but it's not working as expected. I'm getting the error multiple updates to a row by the same query is not allowed. For some reason, it's working on fiddle.

UPDATE test   
SET val=s.val,updated=s.updated 
FROM (select distinct s1.val,
             s1.updated
      from stg s1,
           test t1 
      WHERE t1.id=s1.id a)s;

If any experts can help how to write the query with this scenario, please do.

Answer 1

Your Update is wrong.

you find in the manual how it should be

UPDATE main m
SET "val" = s."val" , "updated" = s."updated"
FROM stg s
WHERE s."Id" = m."Id"

UPDATE 5

SELECT * FROM main

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

SELECT 5

fiddle

When ID, val and updated are the same you can use

UPDATE main m
SET "val" = s."val" , "updated" = s."updated"
FROM (SELECT DISTINCT "Id", "val", "updated" FROM  stg) s
WHERE s."Id" = m."Id"

UPDATE 5

SELECT * FROM main

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

SELECT 5

fiddle

another approach is to use window functions

UPDATE main m
SET "val" = s."val" , "updated" = s."updated"
FROM (WITH CTE as (SELECT  "Id", "val", "updated" ,
  ROW_NUMBER() OVER(PARTITION BY "Id" ORDER BY "updated" DESC) rn FROM  stg)
  SELECT "Id", "val", "updated" FROM CTE WHERE rn = 1) s
WHERE s."Id" = m."Id"

UPDATE 5

SELECT * FROM main

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

SELECT 5

fiddle