Thread: UPDATE .. FROM

I'm kind of stuck as to why postgresql doesn't understand what I mean in the<br />following queries:<br /><br /><span
style="font-family:courier new,monospace;">UPDATE tag_data td SET td.usage_counter = td.usage_counter + 1
FROM</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">tag_list_tag_dataltd WHERE ltd.tag_id = <a href="http://td.id">td.id</a> AND <a
href="http://ltd.id">ltd.id</a>=  102483;</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">ERROR:  column "td" of relation "tag_data" does not exist</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">LINE 1: UPDATE tag_data
tdSET td.usage_counter = td.usage_counter +...</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">                               ^</span><br /><br />I tried without
aliases:<br/><br /><span style="font-family: courier new,monospace;">UPDATE tag_data SET tag_data.usage_counter =
tag_data.usage_counter+ 1 FROM</span><br style="font-family: courier new,monospace;" /><span style="font-family:
couriernew,monospace;">tag_list_tag_data ltd WHERE ltd.tag_id = tag_data.id AND <a href="http://ltd.id">ltd.id</a>
=  102483;</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">ERROR:  column"tag_data" of relation "tag_data" does not exist</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">LINE 1: UPDATE tag_data SET tag_data.usage_counter =
tag_data.usage_...</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">                            ^</span><br/><br />Without a table specifier the error is
understandable:<br/><br /><span style="font-family: courier new,monospace;">UPDATE tag_data SET usage_counter =
usage_counter+ 1 FROM tag_list_tag_data</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">ltd WHERE ltd.tag_id = tag_data.id AND <a href="http://ltd.id">ltd.id</a>
=  102483;</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">ERROR:  columnreference "usage_counter" is ambiguous</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">LINE 1: UPDATE tag_data SET usage_counter =
usage_counter+ 1 FROM t...</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">                                            ^</span><brstyle="font-family: courier new,monospace;"
/><br/>The non-FROM form works:<br /><br /><span style="font-family: courier new,monospace;">UPDATE tag_data SET
usage_counter= usage_counter + 1 WHERE id IN (SELECT</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">tag_id FROM tag_list_tag_data WHERE id =  102483);</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">UPDATE 1</span><br /><br
/>Tableschemata:<br /><br /><span style="font-family: courier new,monospace;"># \d+ tag_data</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier
new,monospace;">                                          Table "public.tag_data"</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">    Column    
|          Type          |                      Modifiers                       | Description</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier
new,monospace;">---------------+------------------------+-------------------------------------------------------+-------------</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> id            |
bigint                | not null default nextval('tag_data_id_seq'::regclass) |</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;"> ns_id         | bigint                 | not
null                                              |</span><brstyle="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> name          | character varying(128) | not
null                                              |</span><brstyle="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> usage_counter | bigint                 | not null default
0                                    |</span><brstyle="font-family: courier new,monospace;" /><span style="font-family:
couriernew,monospace;">Indexes:   </span><br style="font-family: courier new,monospace;" /><span style="font-family:
couriernew,monospace;">    "tag_data_pkey" PRIMARY KEY, btree (id)</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">    "tag_data_ns_id_key" UNIQUE, btree (ns_id,
name)</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"># \d+ tag_list_tag_data   </span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">                                   Table
"public.tag_list_tag_data"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">    Column    |  Type  |                           Modifiers                            |
Description</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">---------------+--------+----------------------------------------------------------------+-------------</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> id            | bigint
|not null default nextval('tag_list_tag_data_id_seq'::regclass) |</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> list_id       | bigint | not
null                                                      |</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> tag_id        | bigint | not
null                                                      |</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> usage_counter | bigint | not null default
0                                            |</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">Indexes:</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">    "tag_list_tag_data_pkey" PRIMARY KEY, btree (id)</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier
new,monospace;">    "tag_list_tag_data_list_id_key"UNIQUE, btree (list_id, tag_id)</span><br /><br />This is 8.3.0.<br
/><br/>Thanks<br /><br />-- <br />Markus Bertheau<br />Blog: <a
href="http://www.bluetwanger.de/blog/">http://www.bluetwanger.de/blog/</a><br/> 

"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