0

I can't figure out how to do this in Postgres.

I have two tables, foo and bar:

foo:id, number

bar:id, foo_id

There is an entry in bar that corresponds to and entry in foo. I want to update foo and return the bar id when I do so. I though something like this would work, but I'm having a very difficult time getting join updates to work in Postgres.

I want to do something like this:

UPDATE 
    foo f 
INNER JOIN
    bar b 
ON 
    f.id = b.foo_id 
SET 
    f.number = 1 
RETURNING 
    b.id

Thanks.

Alexander Kleinhans
  • 5,950
  • 10
  • 55
  • 111

1 Answers1

1

Your syntax is off, try this instead:

UPDATE foo AS f
SET number = 1
FROM bar AS b
WHERE f.id = b.foo_id
RETURNING b.id

There are many references out there which could have shown you how to phrase an update join in Postgres, Stack Overflow being one of them.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360