Message-ID: <3DFEEFB1.371BA57C@rodos.fzk.de>
Date: Tue, 17 Dec 2002 10:34:42 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: andresjavier.garcia@wanadoo.es
Cc: pgsql-sql@postgresql.org
Subject: Re: UPDATE with a SELECT and subSELECT(About comparing dates and non
	dates data)
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

> My problem is that I need to fill in the gaps (the available rain data
in the
> corresponding stations), and this would be a very good output for me.
> I've prepared an UPDATE but it doesn't work. Perhaps someone could
tell me
> where is the error (I've not a very good knowledge of Postgresql). The
UPDATE
> is based on the results of the query:
>
>
----------------------------------------------------------------------------

> UPDATE  series_lluvia SET st7237=rain FROM
>
> /* here begins the SELECT to obtain the series for one rain gauge
station;
> and it works right
>  from here to the next comment */
> SELECT cod_station, year, month, day, rain FROM (
>
> SELECT cod_variable, cod_station, year, month, 1 as day, rain_day1 as
rain
> FROM pluviometria WHERE ten=1
> UNION ALL
> ...
> SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11
as rain
> FROM pluviometria WHERE ten=3 AND rain_day11 IS NOT NULL
> ORDER BY cod_station, year, month, day) AS temp WHERE cod_station=7238

>
> /* the SELECT has finished here */
> WHERE series_lluvia.year=temp.year AND series_lluvia.month=temp.month
AND
> series_lluvia.day=temp.day;
> -------------------------------------------------------------------
>
> Please can you tell me if the syntax of this UPDATE is correct? (Can I
use
> the results of a SELECT to UPDATE a table?)
>
Javier,
I've seen something similar on the list in January this year.
Maybe it works. Try

UPDATE  series_lluvia SET st7237=(
 SELECT rain FROM (

 SELECT cod_variable, cod_station, year, month, 1 as day, rain_day1 as
rain
 FROM pluviometria WHERE ten=1
 UNION ALL
 ...
 SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11 as
rain
 FROM pluviometria WHERE ten=3 AND rain_day11 IS NOT NULL
 ORDER BY cod_station, year, month, day) AS temp WHERE cod_station=7238
 )
 WHERE series_lluvia.year=temp.year AND series_lluvia.month=temp.month
AND
 series_lluvia.day=temp.day;

Regards, Christoph