Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

How to update rows in a loop

Dieses Thema im Forum "PostgreSQL" wurde erstellt von robertdbuckley, 4 September 2012.

  1. robertdbuckley

    robertdbuckley Neuer Benutzer

    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


     
  2. ukulele

    ukulele Datenbank-Guru

    No reason for constructing a "loop", since I'm not sure what you exactly mean by that. If you tried this:
    Code:
    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:
    Code:
    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.
    Code:
    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):
    Code:
    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
     
    PLSQL_SQL und Walter gefällt das.
  3. robertdbuckley

    robertdbuckley Neuer Benutzer

    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))
     
  4. ukulele

    ukulele Datenbank-Guru

    Well then it's more a problem of choosing the right datatype :)
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden