Thread: Bug #938: Wrong UPDATE if exist INNER JOIN and alias for table
Sergey Tikhonenko (tserge@dms.omskcity.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. Sample Code psql 7.3.1 ----------begin-------------------- dis=# CREATE TABLE test1(id int, extid int, value int); CREATE TABLE dis=# INSERT INTO test1 values(1,1,5); INSERT 259479 1 dis=# INSERT INTO test1 values(1,2,6); INSERT 259480 1 dis=# INSERT INTO test1 values(1,3,7); INSERT 259481 1 dis=# INSERT INTO test1 values(2,1,8); INSERT 259482 1 dis=# INSERT INTO test1 values(2,2,9); INSERT 259483 1 dis=# INSERT INTO test1 values(2,3,10); INSERT 259484 1 dis=# CREATE TABLE test2(extid int); CREATE TABLE dis=# INSERT INTO test2 values(1); INSERT 259487 1 dis=# INSERT INTO test2 values(2); INSERT 259488 1 dis=# SELECT test1.* FROM test1 INNER JOIN test2 ON test1.extid=test2.extid WHERE test1.id=1; id | extid | value ----+-------+------- 1 | 1 | 5 1 | 2 | 6 (records: 2) dis=# UPDATE test1 SET value = 10 FROM test1 t1 INNER JOIN test2 ON t1.extid=test2.extid WHERE t1.id=1; UPDATE 6 ----------begin my comment-------------------- !!! WRONG !!! ----------end my comment-------------------- dis=# UPDATE test1 SET value = 10 FROM test2 WHERE test1.extid=test2.extid AND test1.id=1; UPDATE 2 ----------begin my comment-------------------- !!! TRUE !!! ----------end my comment-------------------- dis=# UPDATE test1 SET value = 10 FROM test1 INNER JOIN test2 ON test1.extid=test2.extid WHERE test1.id=1; ERROR: Table name "test1" specified more than once ----------end-------------------- No file was uploaded with this report
pgsql-bugs@postgresql.org writes:
> 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".
Yup, that's what it should do. "FROM test1 t1" adds an additional table
reference that's unconnected to the update target table. You should
have written
UPDATE test1 SET value = 10
FROM test2 WHERE test1.extid=test2.extid AND test1.id=1;
regards, tom lane
On Tue, 8 Apr 2003 pgsql-bugs@postgresql.org wrote: > Sergey Tikhonenko (tserge@dms.omskcity.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)
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
On Sat, 12 Apr 2003, Sergey Tikhonenko wrote: > 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? We handle this extension to the spec differently. Neither syntax is part of SQL92/99 AFAICT and we made different assumptions about what behavior was reasonable.