Thread: UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)
UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)
From
javier garcia
Date:
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
Re: UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)
From
Christoph Haller
Date:
> 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 rainFROM pluviometria WHERE ten=1UNION ALL...SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11 as rainFROM pluviometria WHERE ten=3 AND rain_day11 IS NOT NULLORDER BY cod_station, year, month, day) AS temp WHERE cod_station=7238)WHEREseries_lluvia.year=temp.year AND series_lluvia.month=temp.month ANDseries_lluvia.day=temp.day; Regards, Christoph
Re: UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)
From
javier garcia
Date:
Thanks Christoph; 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 asrainFROMpluviometria WHERE ten=1 UNION ALL...SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11 asrainFROM 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); ------------------------------------------------------
Re: UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)
From
Christoph Haller
Date:
> > 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