From: | "Markus Bertheau" <mbertheau(dot)pg(at)googlemail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | UPDATE .. FROM |
Date: | 2008-03-07 13:13:19 |
Message-ID: | 684362e10803070513sc3781d6ud05019831afd570d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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/
From | Date | Subject | |
---|---|---|---|
Next Message | Yura Gal | 2008-03-07 13:23:36 | Re: RETURN QUERY generates error |
Previous Message | Bart Degryse | 2008-03-07 08:54:09 | Re: Composite UNIQUE across two tables? |