Re: UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)

> 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