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

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)