From: | Franco Bruno Borghesi <franco(at)akyasociados(dot)com(dot)ar> |
---|---|
To: | Igor Kryltsov <kryltsov(at)yahoo(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres update with self join |
Date: | 2004-08-10 20:40:54 |
Message-ID: | 1092170453.1958.4.camel@taz.oficina |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Ochs | 2004-08-10 20:40:55 | 7.4.3 server panic |
Previous Message | Andrew Ayers | 2004-08-10 20:35:46 | Re: Problems with MS Visual Basic 6.0 |