[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] 3 tables join update
From:       rihad <rihad () mail ! ru>
Date:       2007-09-30 9:01:30
Message-ID: 46FF65EA.6050705 () mail ! ru
[Download RAW message or body]

Richard Broersma Jr wrote:
> --- 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.
> 
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
[prev in list] [next in list] [prev in thread] [next in thread]