Hosting Provided By

[GENERAL] 3 tables join update

From: rihad <rihad(at)mail.ru>
Date: Sat Sep 29 2007 - 14:03:49 EDT

Hi,

Say I want to update table Foo with data in table Bar iff left join between Foo and yet another table Baz finds no match.

UPDATE Foo foo LEFT JOIN Bar bar ON (foo.bar_id=bar.id) SET foo.baz_id=baz.id
FROM Baz baz
WHERE bar.id IS NULL;

ERROR: syntax error at or near "LEFT"

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;

ERROR: invalid reference to FROM-clause entry for table "foo" HINT: There is an entry for table "foo", but it cannot be referenced from this part of the query.

Is it possible to rewrite this so that it does what I want in a single query? Important: performance matters.

Thanks.

---------------------------(end of broadcast)---------------------------

TIP 3: Have you checked our extensive FAQ?

Do you need help?

http://www.postgresql.org/docs/faq Received on Sat Sep 29 14:07:36 2007

This message: [ Message body ]
Next message: Richard Broersma Jr: "Re: [GENERAL] 3 tables join update"
Previous message: Hristo Filipov: "Re: [GENERAL] TSearch - Bulgarian"
Next in thread: Richard Broersma Jr: "Re: [GENERAL] 3 tables join update"
Reply: Richard Broersma Jr: "Re: [GENERAL] 3 tables join update"

Contemporary messages sorted: [ By Date ] [ By Thread ] [ By Subject ] [ By Author ] [ By messages with attachments ]

This archive was generated by hypermail 2.1.8 : Sun Oct 07 2007 - 11:58:18 EDT