Re: Postgres update with self join

This is the way you do it in postgreSQL:

UPDATE
test
SET
code=T2.code
FROM
test T2
WHERE
test.code=0 AND
test.master=T2.name;

you need to specify the join condition in the WHERE clause.

On Tue, 2004-08-10 at 00:34, Igor Kryltsov wrote:

> Hi,
>
>
> If you can help me to correct my mistake.
> To simplify my question:
>
> I have table:
>
> create table test (
> name varchar(10),
> code integer,
> master varchar(10));
>
> I have values:
> insert into test values ('ABC', 15074, null);
> insert into test values ('ABC1', 0, 'ABC');
> insert into test values ('ABC2', 0, 'ABC');
> insert into test values ('EKL', 15075, null);
> insert into test values ('EKL1', 0, 'EKL');
>
>
> 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;
>
>
> select * from test;
> name | code | master
> ------+-------+--------
> ABC | 15074 |
> ABC1 | 15074 | ABC
> ABC2 | 15074 | ABC
> EKL | 15074 |
> EKL1 | 15074 | EKL
> (5 rows)
>
> ... all values 15074.
>
>
> Thank you,
>
>
> Igor
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>