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

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));