Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 998442E01D2 for ; Fri, 7 Mar 2008 09:20:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 49831-02 for ; Fri, 7 Mar 2008 09:20:20 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.229]) by postgresql.org (Postfix) with ESMTP id 88B182E018C for ; Fri, 7 Mar 2008 09:20:20 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id c49so84999wra.19 for ; Fri, 07 Mar 2008 05:20:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=L5c8u6cm2gUl1bfATt+41MkHUERsXoVND15o+3IrK6A=; b=N6h5kArXFXvWKCerIMZ9fSn++12WriBiie8Yncizu1s5BtFBIHwo9RUJPp4o3qIxvmKSW4FReCFYXLE4IugGfJPJUrPimsadXyfT5tce0tpbI/QJjlLOVGlHGUQ+934xGLrLV9wmE9qVdS3bvgUBrSMjwj0a+Q9tmA+v5S+vJjM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=t22Snwstwy75Fr/ERZML7Op4/KPkYw6rjQxOIhhQKsPNd86CljXY2QhEmllDYjNGqzIZY37QR45glMUH1d3MmrIqa3GvZuYDyFSS0DdwNzh1WuuLbk5TpG1WlNt/N3ElJY4aopFeTgd4/DzVo/1/PT+WKLcU/Ig5JTOQ1br9WT8= Received: by 10.140.82.40 with SMTP id f40mr456756rvb.0.1204895599674; Fri, 07 Mar 2008 05:13:19 -0800 (PST) Received: by 10.141.169.5 with HTTP; Fri, 7 Mar 2008 05:13:19 -0800 (PST) Message-ID: <684362e10803070513sc3781d6ud05019831afd570d@mail.gmail.com> Date: Fri, 7 Mar 2008 19:13:19 +0600 From: "Markus Bertheau" To: pgsql-sql@postgresql.org Subject: UPDATE .. FROM MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_35442_17820641.1204895599658" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.079 tagged_above=0 required=5 tests=AWL=-1.728, HTML_MESSAGE=0.001, SUBJ_ALL_CAPS=1.806 X-Spam-Level: X-Archive-Number: 200803/40 X-Sequence-Number: 30368 ------=_Part_35442_17820641.1204895599658 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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/ ------=_Part_35442_17820641.1204895599658 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I'm kind of stuck as to why postgresql doesn't understand what I me= an in the
following queries:

UPDATE tag_data td SET td.usage_counter =3D td.usage_counte= r + 1 FROM
tag_list_tag_data ltd W= HERE ltd.tag_id =3D td.id AND ltd.id =3D  102483;
ERROR:  colum= n "td" of relation "tag_data" does not exist
LINE 1: UPDATE tag_data td SET td.usage_counter =3D = td.usage_counter +...
    = ;            &n= bsp;            = ;  ^

I tried without aliases:

UPDATE tag_data SET tag_data.usage_counter= =3D tag_data.usage_counter + 1 FROM
tag_list_tag_data ltd W= HERE ltd.tag_id =3D tag_data.id AND ltd.id = =3D  102483;
ERROR:  c= olumn "tag_data" of relation "tag_data" does not exist<= /span>
LINE 1: UPDATE tag_data= SET tag_data.usage_counter =3D tag_data.usage_...
           =             &nb= sp;    ^

Without a table specifier the error is understandable:

UPDATE tag_data SET usage_counte= r =3D usage_counter + 1 FROM tag_list_tag_data
ltd WHERE ltd.tag_id = =3D tag_data.id AND ltd.id =3D  102= 483;
ERROR:  column reference &q= uot;usage_counter" is ambiguous
LINE 1: UPDATE tag_data= SET usage_counter =3D usage_counter + 1 FROM t...
           =             &nb= sp;            =         ^

The non-FROM form works:

UPDATE tag_data SET usage_counter =3D usage_counter + 1 WHERE = id IN (SELECT
tag_id FROM tag_list_tag_dat= a WHERE id =3D  102483);
UPDATE 1

= Table schemata:

= # \d+ tag_data
    &nb= sp;            =             &nb= sp;            = Table "public.tag_data"
    = ;Column     |       =    Type         = ; |           &= nbsp;           Modifiers=             &nb= sp;          | Description
---------------+------------------------+----= ---------------------------------------------------+------------- id   &n= bsp;        | bigint  &nb= sp;            =   | not null default nextval('tag_data_id_seq'::regclass) |
ns_id      &n= bsp;  | bigint         &n= bsp;       | not null    =             &nb= sp;            =             &nb= sp;    |
name   =        | character varying(128) | not nu= ll            &= nbsp;           &nbs= p;            &= nbsp;        |
usage_counter | bigint      &nb= sp;          | not null defaul= t 0            =             &nb= sp;           |
Indexes:  
    "tag_data_pkey= " PRIMARY KEY, btree (id)
    = ;"tag_data_ns_id_key" UNIQUE, btree (ns_id, name)

# \d+ t= ag_list_tag_data  
    = ;            &n= bsp;            = ;      Table "public.tag_list_tag_data"<= /span>
    Column  =    |  Type  |     &n= bsp;            = ;         Modifiers  &nbs= p;            &= nbsp;           &nbs= p;| Description
---------------+-------= -+----------------------------------------------------------------+--------= -----
id     &= nbsp;      | bigint | not null default nextva= l('tag_list_tag_data_id_seq'::regclass) |
list_id  &nb= sp;    | bigint | not null     &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           |<= br style=3D"font-family: courier new,monospace;"> tag_id       =  | bigint | not null        &n= bsp;            = ;            &n= bsp;            = ;         |
usage_counter | bigint= | not null default 0         =             &nb= sp;            =            |
Indexes:
    = ;"tag_list_tag_data_pkey" PRIMARY KEY, btree (id)
    "tag_list_tag_data_list_id_k= ey" UNIQUE, btree (list_id, tag_id)

This is 8.3.0.

Thanks

--
Markus Bertheau
Blog: http://www.bluetwanger.de/blog/
------=_Part_35442_17820641.1204895599658--