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/
