Message-ID: <3E02EF6C.D048C465@rodos.fzk.de>
Date: Fri, 20 Dec 2002 11:22:37 +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

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