From: Markus Bertheau <mbertheau.pg@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: UPDATE .. FROM
Date: Fri, 7 Mar 2008 19:13:19 +0600
Message-ID: <684362e10803070513sc3781d6ud05019831afd570d@mail.gmail.com> (raw)
I'm kind of stuck as to why postgresql doesn't understand what I mean in the
following queries:
UPDATE tag_data td SET td.usage_counter = td.usage_counter + 1 FROM
tag_list_tag_data ltd WHERE ltd.tag_id = td.id AND ltd.id = 102483;
ERROR: column "td" of relation "tag_data" does not exist
LINE 1: UPDATE tag_data td SET td.usage_counter = td.usage_counter +...
^
I tried without aliases:
UPDATE tag_data SET tag_data.usage_counter = tag_data.usage_counter + 1 FROM
tag_list_tag_data ltd WHERE ltd.tag_id = tag_data.id AND ltd.id = 102483;
ERROR: column "tag_data" of relation "tag_data" does not exist
LINE 1: UPDATE tag_data SET tag_data.usage_counter = tag_data.usage_...
^
Without a table specifier the error is understandable:
UPDATE tag_data SET usage_counter = usage_counter + 1 FROM tag_list_tag_data
ltd WHERE ltd.tag_id = tag_data.id AND ltd.id = 102483;
ERROR: column reference "usage_counter" is ambiguous
LINE 1: UPDATE tag_data SET usage_counter = usage_counter + 1 FROM t...
^
The non-FROM form works:
UPDATE tag_data SET usage_counter = usage_counter + 1 WHERE id IN (SELECT
tag_id FROM tag_list_tag_data WHERE id = 102483);
UPDATE 1
Table schemata:
# \d+ tag_data
Table "public.tag_data"
Column | Type |
Modifiers | Description
---------------+------------------------+-------------------------------------------------------+-------------
id | bigint | not null default
nextval('tag_data_id_seq'::regclass) |
ns_id | bigint | not
null |
name | character varying(128) | not
null |
usage_counter | bigint | not null default
0 |
Indexes:
"tag_data_pkey" PRIMARY KEY, btree (id)
"tag_data_ns_id_key" UNIQUE, btree (ns_id, name)
# \d+ tag_list_tag_data
Table "public.tag_list_tag_data"
Column | Type |
Modifiers | Description
---------------+--------+----------------------------------------------------------------+-------------
id | bigint | not null default
nextval('tag_list_tag_data_id_seq'::regclass) |
list_id | bigint | not
null |
tag_id | bigint | not
null |
usage_counter | bigint | not null default
0 |
Indexes:
"tag_list_tag_data_pkey" PRIMARY KEY, btree (id)
"tag_list_tag_data_list_id_key" UNIQUE, btree (list_id, tag_id)
This is 8.3.0.
Thanks
--
Markus Bertheau
Blog: http://www.bluetwanger.de/blog/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mbertheau.pg@googlemail.com
Subject: Re: UPDATE .. FROM
In-Reply-To: <684362e10803070513sc3781d6ud05019831afd570d@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox