Re: [GENERAL] Update Join Query

2008-06-23 Thread Craig Ringer 

Daniel Futerman wrote:


Is it possible to have UPDATE JOIN queries in PostgreSQL?


Yes:

UPDATE target

FROM othertable;

As far as I know Pg can only do an inner join on the update target. This 
can be easily be turned into an outer join with something like:


UPDATE target
   
FROM target t LEFT OUTER JOIN othertable
WHERE target.id = t.id;

or similar. I haven't checked to see whether this results in an extra 
scan in the query plan; you might want to use EXPLAIN ANALYZE to examine 
how Pg will execute the query.


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Update Join Query

2008-06-23 Thread Antonio Perez 


--- El lun 23-jun-08, Daniel Futerman <[EMAIL PROTECTED]> escribió:
De: Daniel Futerman <[EMAIL PROTECTED]>
Asunto: [GENERAL] Update Join Query
A: pgsql-general@postgresql.org
Fecha: lunes, 23 junio, 2008, 4:43 pm

Hi,

Looking for the correct syntax for an UPDATE LEFT JOIN query in PostgreSQL.

The equivalent MySQL query is :

    UPDATE

        Foo f LEFT JOIN Goo g on f.Foo_ID = g.Goo_ID
    SET

        f.Foo_ID = g.Goo_ID
    WHERE

        f.Foo_ID IS NOT NULL;


 When I try to run this in Postgres, i get the following error:

ERROR:  syntax error at or near "LEFT"


Is it possible to have UPDATE JOIN queries in PostgreSQL?

Thanks.

sorry the last message have a error

try whit this

update foo set Foo_ID = goo.Goo_ID from goo where goo.Goo_id = foo.Foo_id and 
foo_ID IS NOT NULL;

Re: [GENERAL] Update Join Query

2008-06-23 Thread Antonio Perez 


--- El lun 23-jun-08, Daniel Futerman <[EMAIL PROTECTED]> escribió:
De: Daniel Futerman <[EMAIL PROTECTED]>
Asunto: [GENERAL] Update Join Query
A: pgsql-general@postgresql.org
Fecha: lunes, 23 junio, 2008, 4:43 pm

Hi,

Looking for the correct syntax for an UPDATE LEFT JOIN query in PostgreSQL.

The equivalent MySQL query is :

    UPDATE

        Foo f LEFT JOIN Goo g on f.Foo_ID = g.Goo_ID
    SET

        f.Foo_ID = g.Goo_ID
    WHERE

        f.Foo_ID IS NOT NULL;


 When I try to run this in Postgres, i get the following error:

ERROR:  syntax error at or near "LEFT"


Is it possible to have UPDATE JOIN queries in PostgreSQL?

Thanks.


Try this..

UPDATE Foo f 
    SET f.Foo_ID = g.Goo_ID
FROM (
    select g.Goo_ID from Goo g
    RIGHT JOIN Foo f on (f.Foo_ID = g.Goo_ID)
    WHERE f.Foo_ID IS NOT NULL;
    ) g
   

Re: [GENERAL] Update Join Query

2008-06-23 Thread Mark Roberts 
update foo
set foo_id = g.goo_id
from goo g
where foo.foo_id = g.goo_id and foo.foo_id is not null

I think. :)

-Mark

On Mon, 2008-06-23 at 21:43 +0200, Daniel Futerman wrote:
> Hi,
> 
> Looking for the correct syntax for an UPDATE LEFT JOIN query in
> PostgreSQL.
> 
> The equivalent MySQL query is :
> 
> UPDATE
> Foo f LEFT JOIN Goo g on f.Foo_ID = g.Goo_ID
> SET
> f.Foo_ID = g.Goo_ID
> WHERE
> f.Foo_ID IS NOT NULL;
> 
> 
>  When I try to run this in Postgres, i get the following error:
> 
> ERROR:  syntax error at or near "LEFT"
> 
> Is it possible to have UPDATE JOIN queries in PostgreSQL?
> 
> Thanks.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Update Join Query

2008-06-23 Thread Daniel Futerman 
Hi,

Looking for the correct syntax for an UPDATE LEFT JOIN query in PostgreSQL.

The equivalent MySQL query is :

UPDATE
Foo f LEFT JOIN Goo g on f.Foo_ID = g.Goo_ID
SET
f.Foo_ID = g.Goo_ID
WHERE
f.Foo_ID IS NOT NULL;


 When I try to run this in Postgres, i get the following error:

ERROR:  syntax error at or near "LEFT"

Is it possible to have UPDATE JOIN queries in PostgreSQL?

Thanks.