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: Fri, 20 Dec 2002 11:22:37 +0100
Message-ID: <3E02EF6C.D048C465@rodos.fzk.de> (raw)
>
> Typing what you told me there was an errror; I' ve changed it slightly
an it
> seems that this sintax is correct. I say "it seems" because the
computer
> begins to process the query but doesn't finish it. I've leaved it
working for
> more than half an hour, before cancel it, with no result.
>
> Thank you anyway. This is what I think is a good sintax for UPDATE -
SELECT -
> SUBSELECT. Perhaps in mor simple cases it works. May someone is
interested in
> it.
>
> ----------
> Javier
>
> --------------------------------------------------------------------
> 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=7237) AS
> temp2 WHERE series_lluvia.year=temp2.year AND
> series_lluvia.month=temp2.month AND series_lluvia.day=temp2.day);
> ------------------------------------------------------
>
Javier,
I've seen several queries which seemed to run for ages before.
In many cases it helped to generate temporary tables and / or
split up into "smaller" commands.
Have you tried it by removing the union clauses as
UPDATE series_lluvia SET st7237=(
SELECT rain FROM (
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=7237)
AS
temp2 WHERE series_lluvia.year=temp2.year AND
series_lluvia.month=temp2.month AND series_lluvia.day=temp2.day);
If this runs in an acceptable time, split up into several UPDATEs.
If not, think of using temporary tables for SELECT - SUBSELECT.
It's probably useful to do this in a transaction block started by BEGIN;
so you can ROLLBACK; if the result is wrong.
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: <3E02EF6C.D048C465@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