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

>
> 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 NULLORDER BY cod_station, year, month, day) AS temp WHERE
cod_station=7237)
AStemp2  WHERE series_lluvia.year=temp2.year ANDseries_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