From: Christoph Haller <ch@rodos.fzk.de>
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)
Date: Tue, 17 Dec 2002 10:34:42 +0100
Message-ID: <3DFEEFB1.371BA57C@rodos.fzk.de> (raw)
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, andresjavier.garcia@wanadoo.es
Subject: Re: UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)
In-Reply-To: <3DFEEFB1.371BA57C@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox