> My problem is that I need to fill in the gaps (the available rain data in the > corresponding stations), and this would be a very good output for me. > I've prepared an UPDATE but it doesn't work. Perhaps someone could tell me > where is the error (I've not a very good knowledge of Postgresql). The UPDATE > is based on the results of the query: > > ----------------------------------------------------------------------------
> UPDATE series_lluvia SET st7237=rain FROM > > /* here begins the SELECT to obtain the series for one rain gauge station; > and it works right > from here to the next comment */ > SELECT cod_station, year, month, day, 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=7238 > > /* the SELECT has finished here */ > WHERE series_lluvia.year=temp.year AND series_lluvia.month=temp.month AND > series_lluvia.day=temp.day; > ------------------------------------------------------------------- > > Please can you tell me if the syntax of this UPDATE is correct? (Can I use > the results of a SELECT to UPDATE a table?) > Javier, I've seen something similar on the list in January this year. Maybe it works. Try 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=7238 ) WHERE series_lluvia.year=temp.year AND series_lluvia.month=temp.month AND series_lluvia.day=temp.day; Regards, Christoph ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
