UPDATE with a SELECT and subSELECT(About comparing dates and non dates data) - Mailing list pgsql-sql

Thanks Josh and Bret for your answers.
But I think I can try a slightly different thing. As I said,  I've got data, 
extracted from rain gauge stations. Perhaps I wasn't explicit enough. My 
source data are in this format:
cod_variable | cod_station | year | month | ten | rain_day1 | wind_day1 | 
rain_day2 | wind_day2 | rain_day3 | wind_day3 | rain_day4 | wind_day4 | 
rain_day5 | wind_day5 | rain_day6 | wind_day6 | rain_day7 | wind_day7 | 
rain_day8 | wind_day8 | rain_day9 | wind_day9 | rain_day10 | wind_day10 | 
rain_day11 | wind_day11 | ten_sum_rain

--------------+-------------+------+-------+-----+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+------30201
      | 7237        | 1953 |     1 |   1 |         0 |         0 |        0 |         0 |         0 |         0 |
 0 |         0 |              0 |         0 |         0 |         0 |         0 |         0 |              0 |
0|         0 |         0 |         0 |          0 |                |            |         0
 
and this is just a row (ten means that the data belong to the first, second 
or third of the weeks of the month). I've got five gauge stations and daily 
data for about forty years (73000 of these long rows). Missing data are 
non-existent rows; so, when a row doesn't appears I lost ten rain data (the 
rain of ten days).
But with the help of the wonderful people in this list I could extract rain 
series (excellent):
cod_station | year | month | day | rain
-------------+------+-------+-----+------7250        | 1933 |     8 |   1 |    07250        | 1933 |     8 |   2 |
07250       | 1933 |     8 |   3 |    0...
 

So, now, in the result of this query, a missing data translates into several 
non-existent row.

Now, I have prepared a table with a complete series of date field (everyday 
from 1553 to 2004), and made ALTER it to ADD aditional fields for every 
station I have:year | month | day | st7237 | st7238 | st7239 ...
------+-------+-----+---------+---------+---------+---------1953 |     1 |   1 |         |         |         |1953 |
1 |   2 |         |         |         |1953 |     1 |   3 |         |         |         |...
 

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 rightfrom 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, 2 as day, rain_day2 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 3 as day, rain_day3 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 4 as day, rain_day4 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 5 as day, rain_day5 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 6 as day, rain_day6 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 7 as day, rain_day7 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 8 as day, rain_day8 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 9 as day, rain_day9 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 10 as day, rain_day10 as rain 
FROM pluviometria WHERE ten=1
UNION ALL
SELECT cod_variable, cod_station, year, month, 11 as day, rain_day1 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 12 as day, rain_day2 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 13 as day, rain_day3 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 14 as day, rain_day4 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 15 as day, rain_day5 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 16 as day, rain_day6 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 17 as day, rain_day7 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 18 as day, rain_day8 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 19 as day, rain_day9 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 20 as day, rain_day10 as rain 
FROM pluviometria WHERE ten=2
UNION ALL
SELECT cod_variable, cod_station, year, month, 21 as day, rain_day1 as rain 
FROM pluviometria WHERE ten=3
UNION ALL
SELECT cod_variable, cod_station, year, month, 22 as day, rain_day2 as rain 
FROM pluviometria WHERE ten=3
UNION ALL
SELECT cod_variable, cod_station, year, month, 23 as day, rain_day3 as rain 
FROM pluviometria WHERE ten=3
UNION ALL
SELECT cod_variable, cod_station, year, month, 24 as day, rain_day4 as rain 
FROM pluviometria WHERE ten=3
UNION ALL
SELECT cod_variable, cod_station, year, month, 25 as day, rain_day5 as rain 
FROM pluviometria WHERE ten=3
UNION ALL
SELECT cod_variable, cod_station, year, month, 26 as day, rain_day6 as rain 
FROM pluviometria WHERE ten=3
UNION ALL
SELECT cod_variable, cod_station, year, month, 27 as day, rain_day7 as rain 
FROM pluviometria WHERE ten=3
UNION ALL
SELECT cod_variable, cod_station, year, month, 28 as day, rain_day8 as rain 
FROM pluviometria WHERE ten=3
UNION ALL
SELECT cod_variable, cod_station, year, month, 29 as day, rain_day9 as rain 
FROM pluviometria WHERE ten=3 AND rain_day9 IS NOT NULL
UNION ALL
SELECT cod_variable, cod_station, year, month, 30 as day, rain_day10 as rain 
FROM pluviometria WHERE ten=3 AND rain_day10 IS NOT NULL
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?)

Thanks for your help. I know this is too long.

Regards,

Javier