Postgresql – update one column based on query containing another (joined) table

join;postgresqlupdate

Consider the following select query:

SELECT s.id, s.event, s.column, e.oldcolumn
FROM section as s
INNER JOIN event as e
ON s.event = e.id
WHERE s.id = 1

Now I wish to update the section table so that the values are equal to the oldcolumn from the event table.

I tried:

UPDATE public."section"
SET public."section".column = public."event".oldcolumn
FROM public."section"
INNER JOIN public."event"
on public."section".event = public."event".id
WHERE s.id = 1

However this, on postgresql, returned the following error:

ERROR:  table name "section" specified more than once
SQL state: 42712

How would I do such a thing, using joins inside an update query?

Best Answer

As documented in the manual you should not repeat the target table in the FROM clause.

So your UPDATE should be like this:

UPDATE public."section" s
  SET column = evt.oldcolumn
FROM public."event" evt
WHERE s.event = evt.id
  AND s.id = 1;
Related Question