Skip site navigation (1) Skip section navigation (2)

UPDATE .. FROM

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 (view raw or flat)
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/

Responses

pgsql-sql by date

Next:From: Yura GalDate: 2008-03-07 13:23:36
Subject: Re: RETURN QUERY generates error
Previous:From: Bart DegryseDate: 2008-03-07 08:54:09
Subject: Re: Composite UNIQUE across two tables?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group