pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUPDATE .. FROM
2+ messages / 2 participants
[nested] [flat]
* UPDATE .. FROM
@ 2008-03-07 13:13 Markus Bertheau <mbertheau.pg@googlemail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Markus Bertheau @ 2008-03-07 13:13 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: UPDATE .. FROM
@ 2008-03-07 13:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Markus Bertheau <mbertheau.pg@googlemail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2008-03-07 13:47 UTC (permalink / raw)
To: Markus Bertheau <mbertheau.pg@googlemail.com>; +Cc: pgsql-sql
"Markus Bertheau" <mbertheau.pg@googlemail.com> writes:
> 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
You aren't supposed to specify a table name (nor alias) for a target
variable in a SET clause. It's useless (since you can't update more
than one table) and it's ambiguous because of the possibility of
composite fields. What you want is something like
UPDATE tag_data td SET usage_counter = td.usage_counter + 1 FROM ...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-03-07 13:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-07 13:13 UPDATE .. FROM Markus Bertheau <mbertheau.pg@googlemail.com>
2008-03-07 13:47 ` Tom Lane <tgl@sss.pgh.pa.us>
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