Richard Broersma Jr wrote:
--- rihad <rihad@xxxxxxx> 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.
Sorry the query failed victim of me trying to simplify it so I forgot
the foo=bar join. Here's a 2 table join suffering from the same problem:
I want the update only when bar.common_field IS NULL:
UPDATE Foo foo
SET ...
FROM LEFT JOIN Bar bar USING(common_field)
WHERE blah='blah' AND bar.common_field IS NULL;
ERROR: syntax error at or near "JOIN"
I know I'm misusing UPDATE ... FROM because I don't really want Bar's
values to go into Foo, but only using them for a conditional update
(atomically I hope).
---------------------------(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
