Re: update and IN vs. EXISTS

> -----Original Message-----
> From: pginfo [mailto:pginfo(at)t1(dot)unisoftbg(dot)com]
> Sent: Saturday, February 01, 2003 3:50 PM
> To: Bruno Wolff III
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] update and IN vs. EXISTS
>
>
>
>
> Bruno Wolff III wrote:
>
> > On Sat, Feb 01, 2003 at 12:40:00 +0100,
> > pginfo <pginfo(at)t1(dot)unisoftbg(dot)com> wrote:
> > >
> > > If I try to execute:
> > > update Table1 set fieldForUpdate = 1 where ID IN
> (select T2.ID from
> > > Table2);
> > > it is running very slow.
> >
> > You might try:
> > update Table1 set fieldForUpdate = 1 from Table2 where
> Table1.id = Table2.id;
> >
>
> It is great.It takes 122 sec.
> With IN it takes 8000 sec.
>
> > This uses a nonstandard postgres extension and may not be
> portable, if that
> > is a concern.
> >
>
> How to resolve the problem with the standart?regards,
> iavn.
>

This should work as well:

update Table1 set fieldForUpdate = 1 where EXISTS
(select 1 from Table2 where Table1.IDS=Table2.IDS );

Tambet