UPDATE .. FROM - Mailing list pgsql-sql
| From | Markus Bertheau |
|---|---|
| Subject | UPDATE .. FROM |
| Date | |
| Msg-id | 684362e10803070513sc3781d6ud05019831afd570d@mail.gmail.com Whole thread Raw |
| Responses |
Re: UPDATE .. FROM
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-sql |
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/>