Re: Postgres update with self join
От | Franco Bruno Borghesi |
---|---|
Тема | Re: Postgres update with self join |
Дата | |
Msg-id | 1092170453.1958.4.camel@taz.oficina обсуждение исходный текст |
Ответ на | Postgres update with self join ("Igor Kryltsov" <kryltsov@yahoo.com>) |
Список | 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:
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 | ABCABC2 | 0 | ABCEKL | 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 | ABCABC2 | 15074 | ABCEKL | 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
Вложения
В списке pgsql-general по дате отправления: