On Tue, 10 Aug 2004, Igor Kryltsov wrote: > Table looks like: > > select * from test; > name | code | master > ------+-------+-------- > ABC | 15074 | > ABC1 | 0 | ABC > ABC2 | 0 | ABC > EKL | 15075 | > EKL1 | 0 | EKL > (5 rows) > > > Now I need to replace "0" values in "code" column by corresponding "code" > values by following link between "master" field in a record where code=0 and > "name" field where it is not. > By the other words first two 0's have to be replaced with 15074 and last 0 > with 15075. > > This update works in MSSQL but in Postgres it replaces code values as shown > below. > > update test > set code = i1.code > from test i1 > join test i2 on i1.name = i2.master > where i2.code = 0;
You probably need to be constraining the join between test and (i1 join i2). Maybe an additional where clause like "and test.name=i2.name" or something like that would work. Or, I think you can do this with a subselect which would have the advantage of not requiring extensions to the standard. Perhaps something like update test set code=(select code from test i2 where test.master=i2.name) where code=0; would do it. ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
