When I work with MS SQL I write
"UPDATE t1 SET value =3D 10 FROM test1 t1 INNER JOIN test2 ON=20
t1.extid=3Dtest2.extid WHERE t1.id=3D1;"
Why this not work in Postgresql?
=F7 =D3=CF=CF=C2=DD=C5=CE=C9=C9 =CF=D4 8 =E1=D0=D2=C5=CC=D8 2003 21:48 Step=
han Szabo =CE=C1=D0=C9=D3=C1=CC(a):
> On Tue, 8 Apr 2003 pgsql-bugs@postgresql.org wrote:
> > Sergey Tikhonenko (tserge@dms.omskcity.com) reports a bug with a severi=
ty
> > of 2 The lower the number the more severe it is.
> >
> > Short Description
> > Wrong UPDATE if exist INNER JOIN and alias for table
> >
> > Long Description
> > UPDATE test1 SET value =3D 10 FROM test1 t1 INNER JOIN test2 ON
> > t1.extid=3Dtest2.extid WHERE t1.id=3D1;
> >
> > This expression update all record i table "test1". Must update only 2
> > records. See example.
>
> No, this must update all records. The outer test1 in not constrained by
> anything in your from/where. You're joining a second copy of test1
> (aliased t1) with test2 and then joining to test1 with no condition so all
> rows in test1 are affected.
>
> If you need to use it in this form (rather than the from test2 form) you
> need to constrain test1 as well (for example t1.id=3Dtest1.id and
> t1.extid=3Dtest1.extid)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
