Re: Bug #938: Wrong UPDATE if exist INNER JOIN and alias

When I work with MS SQL I write

"UPDATE t1 SET value = 10 FROM test1 t1 INNER JOIN test2 ON
t1.extid=test2.extid WHERE t1.id=1;"

Why this not work in Postgresql?

В сообщении от 8 Апрель 2003 21:48 Stephan Szabo написал(a):
> On Tue, 8 Apr 2003 pgsql-bugs(at)postgresql(dot)org wrote:
> > Sergey Tikhonenko (tserge(at)dms(dot)omskcity(dot)com) reports a bug with a severity
> > 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 = 10 FROM test1 t1 INNER JOIN test2 ON
> > t1.extid=test2.extid WHERE t1.id=1;
> >
> > 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=test1.id and
> t1.extid=test1.extid)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster