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