Re: [GENERAL] 3 tables join update

• rihad <rihad@mail.ru> wrote:
  > UPDATE Foo foo
  > SET foo.baz_id=baz.id
  > FROM Baz baz LEFT JOIN Bar bar ON (foo.bar_id=bar.id)
  > WHERE bar.id IS NULL;
  

This query cannot work. Basiclly, you are trying to set the foo.baz_id = baz.id for records in foo that do not yet exist. Doing this is impossible.

For this to work, you should be using an INSERT query to _ADD_ records to foo that do not yet exist.

Regards,
Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match