[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)
From:       Christoph Haller <ch () rodos ! fzk ! de>
Date:       2002-12-20 10:22:37
[Download RAW message or body]

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html
[prev in list] [next in list] [prev in thread] [next in thread]