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