"Igor Kryltsov" <[EMAIL PROTECTED]> writes: > 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.
> 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; That query has no join condition to the target table, so it's hardly surprising that it updates everything in sight. If it "works" in MSSQL it must be because they are making some weird decision to pretend that one or the other of the mentions of test in the FROM clause ought to be identified with the target table. (If it acts as you want then they must be identifying "test i2" with "test", which is *really* weird --- you would think the first occurrence of test in the FROM would be the one they'd pick. I suppose this is another instance of an implementation bug becoming enshrined as a feature.) In Postgres you want to do something like this: update test set code = mst.code from test mst where test.master = mst.name and test.code = 0; To act exactly as you stated in words you'd probably also want to add "and mst.code <> 0". regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org