How to update rows in a loop

[robertdbuckley]

Hi,

My first thread in this forum!

I having problems constructing a query in postgresql. The query works as a simple select statement as follows...

SELECT (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)) FROM energie.tennet_auswertung_2010

Table: energie.tennet_auswertung_2010
Field :ges_kw_zgb

I would like to update another field based on this result and loop through each field to calculate it for the whole table. However, when I try this I get the error " more than one row returned by a subquery used as an expression"

The expression take the values from the field "ges_kw_zgb" and divides it by the sum of all the values in the same field thus giving the proportion that row is of the total.

So I need to reconstruct the statement and this is where I´m stuck,

Any help would be much appreciated.

Cheers,

Robert

 

[ukulele]

No reason for constructing a "loop", since I'm not sure what you exactly mean by that. If you tried this:

UPDATE    energie.tennet_auswertung_2010
SET        another_field = (
SELECT (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)) FROM energie.tennet_auswertung_2010 -- <-- subquery
)

...in your loop-function you get multiple rows in your subquery and that would provoke your error.

This should work for the whole table, setting your other field once when executed:

UPDATE    energie.tennet_auswertung_2010
SET        another_field = ( ges_kw_zgb / (    SELECT    sum(ges_kw_zgb)
                                            FROM    energie.tennet_auswertung_2010 ) )

However, if you have NULL values somewhere in ges_kw_zgb the sum() function will ignore them but you get an error in your set statement, so you may want to leave those lines out.

UPDATE    energie.tennet_auswertung_2010
SET        another_field = ( ges_kw_zgb / (    SELECT    sum(ges_kw_zgb)
                                            FROM    energie.tennet_auswertung_2010 ) )
WHERE    ges_kw_zgb IS NOT NULL

Maybe you can execute more then one SQL command so you can avoid executing the sum() funktion more then once (SQL may cache it anyway but I'm not sure about that):

DECLARE    @sum INT
SET        @sum = (    SELECT    sum(ges_kw_zgb)
                    FROM    energie.tennet_auswertung_2010 )
 
UPDATE    energie.tennet_auswertung_2010
SET        another_field = ( ges_kw_zgb / @sum )
WHERE    ges_kw_zgb IS NOT NULL

 

[robertdbuckley]

Thanks for the reply. I had to put the Field name in "" and round the result. Worked great though Thanks!

UPDATE energie.tennet_auswertung_2010 SET "Test"=
(SELECT round(100 *(
ges_kw_zgb/(SELECT sum(ges_kw_zgb) FROM energie.tennet_auswertung_2010)
),2))

 

[ukulele]

Well then it's more a problem of choosing the right datatype