Re: What is wrong with this PostgreSQL UPDATE statement??

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Steve Johnson <stevej456(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: What is wrong with this PostgreSQL UPDATE statement??
Date: 2008-08-23 01:41:54
Message-ID: 20080822183519.O68023@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

On Fri, 22 Aug 2008, Steve Johnson wrote:

> update certgroups
> set termgroupname = tg.termgroupname
> from certgroups c, termgroup tg
> where (c.days >= tg.mindays) and (c.days <= tg.maxdays);

In recent PostgreSQL versions I believe this is properly written:

update certgroups c
set termgroupname = tg.termgroupname
from termgroup tg
where (c.days >= tg.mindays) and (c.days <= tg.maxdays);

At least as of SQL2003, I think both of the above use extensions, so
there's no guarantee to the behavior on different systems and to do it
with a standard query, you'd need to use a subselect, something like:

update certgroups c set termgroupname = (select termgroupname from
termgroup tg where (c.days >= tg.mindays) and (c.days <=tg.maxdays));

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2008-08-23 02:50:10 Re: What is wrong with this PostgreSQL UPDATE statement??
Previous Message Steve Johnson 2008-08-23 00:59:27 What is wrong with this PostgreSQL UPDATE statement??