Re: UPDATE .. FROM

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Markus Bertheau" <mbertheau(dot)pg(at)googlemail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: UPDATE .. FROM
Date: 2008-03-07 13:47:41
Message-ID: 12016.1204897661@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

"Markus Bertheau" <mbertheau(dot)pg(at)googlemail(dot)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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Jamie Tufnell 2008-03-07 15:33:28 Re: Composite UNIQUE across two tables?
Previous Message Yura Gal 2008-03-07 13:23:36 Re: RETURN QUERY generates error