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

Anfänger kämpft mit update Funktion incl. Subselect. Für Profis sicher ein Klacks...

Dieses Thema im Forum "Oracle" wurde erstellt von atlantis, 13 Dezember 2016.

  1. atlantis

    atlantis Benutzer

    Hallo Forum

    Habe eigentlich ein lächerliches Thema, das mich aber schon viel Zeit gekostet hat.

    Folgende Tabelle:
    T_TRAD_UPE_SB mit folgenden Spalten:
    • UPE_PK
    • Z_TLN
    • GUELTIG_AB
    • CREATED_BY
    • CREATED_ON
    • LAST_UPDATE_BY
    • LAST_UPDATE_ON
    • UPE_QUELLE
    • UPE
    • LATEST
    • MAX
    • MIN
    ZIEL:
    Jetzt möchte ich mit Update das Feld max = 1 setzen, bei den Datensätzen (es können mehrere DS mit gleichem Wert sein) die in der Gruppe(GROUP BY Z_TLN, UPE_QUELLE, GUELTIG_AB) den maximalen UPE haben.

    Ich schaffe es nicht, den Subselect so zu formulieren, dass ich die ID´s der betroffenen Datensätze (UPE_PK) mit dem höchten UPE der Gruppe bekomme.

    also so ungefähr wie:
    update T_TRAD_UPE_SB t1
    SET t1.MAX = 1
    WHERE t1."UPE_PK" = t2."UPE_PK"
    AND
    t1."UPE" IN (SELECT Max(t2.UPE) FROM T_TRAD_UPE_SB t2 GROUP BY t2."Z_TLN", t2."UPE_QUELLE", t2."GUELTIG_AB") ;

    Vielen Dank für einen kurzen Tip...

    Atlantis
     
  2. akretschmer

    akretschmer Datenbank-Guru

    gehe schrittweise vor. Erstelle zuerst einmal in Select, was die richtigen Datensäzte liefert.

    Z.B. (untested)

    select Z_TLN, UPE_QUELLE, GUELTIG_AB, max(UPE) from ... group by Z_TLN, UPE_QUELLE, GUELTIG_AB.
     
  3. atlantis

    atlantis Benutzer

    Hallo akretschmer

    Vielen Dank für den Tip. Das hatte ich bereits probiert.

    select max(UPE) FROM T_TRAD_UPE_SB GROUP BY Z_TLN, UPE_QUELLE, GUELTIG_AB; funktioniert.

    Mein Hänger ist: Wie komme ich wenn ich diese Max Werte habe auf die ID des jeweiligen Datensatzes?


    Danke !
     
  4. akretschmer

    akretschmer Datenbank-Guru

    mein select sah aber anders aus, es lieferte auch die Keys dazu. Die kannst Du später dann nutzen.

    Folgendes geht mit PostgreSQL, aber vermutlich nicht mit Deiner DB:

    Du hast:

    Code:
    test=*# select * from atlantis ;
     pk | g1 | g2 | val | flag
    ----+----+----+-----+------
      1 |  1 |  1 |  10 | f
      2 |  1 |  1 |  20 | f
      3 |  1 |  1 |  30 | f
      4 |  1 |  2 |  12 | f
      5 |  1 |  4 |  15 | f
      6 |  1 |  4 |  25 | f
    (6 Zeilen)
    
    pk ist der Primary Key, g1 und g2 bilden eine Gruppe (wenn gleich), val die Variable, deren Maximum wir suchen (per gruppe) und flag wollen wir da setzen, wo je Gruppe val das Max hat.

    Code:
    --
    -- das sind unsere Datensätze
    --
    test=*# select distinct on (g1,g2) pk, g1, g2, val from atlantis order by g1, g2, val desc;
     pk | g1 | g2 | val
    ----+----+----+-----
      3 |  1 |  1 |  30
      4 |  1 |  2 |  12
      6 |  1 |  4 |  25
    (3 Zeilen)
    
    --
    -- wir können das auf nur die pk-spalte reduzieren
    --
    test=*# select distinct on (g1,g2) pk from atlantis order by g1, g2, val desc;
     pk
    ----
      3
      4
      6
    (3 Zeilen)
    
    --
    -- und das zum Update nutzen
    --
    test=*# update atlantis set flag = true where pk in (select distinct on (g1,g2) pk from atlantis order by g1, g2, val desc);
    UPDATE 3
    
    --
    -- Kontrolle
    --
    test=*# select * from atlantis ;
     pk | g1 | g2 | val | flag
    ----+----+----+-----+------
      1 |  1 |  1 |  10 | f
      2 |  1 |  1 |  20 | f
      5 |  1 |  4 |  15 | f
      3 |  1 |  1 |  30 | t
      4 |  1 |  2 |  12 | t
      6 |  1 |  4 |  25 | t
    (6 Zeilen)
    
     
  5. atlantis

    atlantis Benutzer

    Hallo akretschmer

    Danke für Deine wertvollen Tips und vielen Dank, dass Du Dir so viel Mühe gemacht hast mit Beispieldatensätzen usw. !!!!
    Leider wuppt das nicht wie vorgeschlagen. Anbei meine Versuchsergebnisse...
    Originaltext in schwarz:


    mein select sah aber anders aus, es lieferte auch die Keys dazu. Die kannst Du später dann nutzen.
    Analog Deines Vorschlages liefert: select Z_TLN, UPE_QUELLE, GUELTIG_AB, max(UPE) from T_TRAD_UPE_SB group by Z_TLN, UPE_QUELLE, GUELTIG_AB das gewünschte Ergebnis.
    So weit so gut. Jetzt kommt wieder mein Problem, dass ich zu diesen ausgewählten DS die jeweiligen pk´s (UPE_PK) gerne hätte.

    Wenn ich jetzt allerdings versuche den pk UPE_PK ins Spiel zu bringen beginnt mein Problem:
    select UPE_PK, Z_TLN, UPE_QUELLE, GUELTIG_AB, max(UPE) from T_TRAD_UPE_SB group by Z_TLN, UPE_QUELLE, GUELTIG_AB
    bekomme ich die Fehlermeldung:
    SQL-Fehler: ORA-00979: Kein GROUP BY-Ausdruck
    00979. 00000 - "not a GROUP BY expression"


    Folgendes geht mit PostgreSQL, aber vermutlich nicht mit Deiner DB:
    Da kannst Du recht haben. Ich arbeite mit Oracle.

    Du hast:
    test=*# select * from atlantis ;
    pk | g1 | g2 | val | flag
    ----+----+----+-----+------
    1 | 1 | 1 | 10 | f
    2 | 1 | 1 | 20 | f
    3 | 1 | 1 | 30 | f
    4 | 1 | 2 | 12 | f
    5 | 1 | 4 | 15 | f
    6 | 1 | 4 | 25 | f

    pk ist der Primary Key, g1 und g2 bilden eine Gruppe (wenn gleich), val die Variable, deren Maximum wir suchen (per gruppe) und flag wollen wir da setzen, wo je Gruppe val das Max hat.

    -- das sind unsere Datensätze
    --
    test=*# select distinct on (g1,g2) pk, g1, g2, val from atlantis order by g1, g2, val desc;
    pk | g1 | g2 | val
    ----+----+----+-----
    3 | 1 | 1 | 30
    4 | 1 | 2 | 12
    6 | 1 | 4 | 25

    Den Versuch habe ich übertragen. Bei mir sieht das dann so aus:
    select distinct on (Z_TLN, UPE_QUELLE, GUELTIG_AB),UPE_PK from T_TRAD_UPE_SB order by Z_TLN, UPE_QUELLE, GUELTIG_AB desc;

    und erzeugt diese Fehlermeldung:
    SQL-Fehler: ORA-00936: Ausdruck fehlt
    00936. 00000 - "missing expression"



    -- wir können das auf nur die pk-spalte reduzieren
    test=*# select distinct on (g1,g2) pk from atlantis order by g1, g2, val desc;
    pk
    ----
    3
    4
    6

    analog: select distinct on (Z_TLN, UPE_QUELLE, GUELTIG_AB) UPE from atlantis order by Z_TLN, UPE_QUELLE, GUELTIG_AB, UPE desc;
    liefert gleichen Fehler:
    SQL-Fehler: ORA-00936: Ausdruck fehlt
    00936. 00000 - "missing expression"


    -- und das zum Update nutzen
    test=*# update atlantis set flag = true where pk in (select distinct on (g1,g2) pk from atlantis order by g1, g2, val desc);
    UPDATE 3

    -- Kontrolle
    test=*# select * from atlantis ;
    pk | g1 | g2 | val | flag
    ----+----+----+-----+------
    1 | 1 | 1 | 10 | f
    2 | 1 | 1 | 20 | f
    5 | 1 | 4 | 15 | f
    3 | 1 | 1 | 30 | t
    4 | 1 | 2 | 12 | t
    6 | 1 | 4 | 25 | t


    Hast Du noch einen alternativen Tip?

    Danke!

    Atlantis
     
  6. akretschmer

    akretschmer Datenbank-Guru

    weil du es bist ...

    Code:
    test=# select * from atlantis ;
     pk | g1 | g2 | val | flag
    ----+----+----+-----+------
      1 |  1 |  1 |  10 | f
      2 |  1 |  1 |  20 | f
      5 |  1 |  4 |  15 | f
      3 |  1 |  1 |  30 | f
      4 |  1 |  2 |  12 | f
      6 |  1 |  4 |  25 | f
    (6 Zeilen)
    
    test=*# update atlantis set flag = true from (select g1, g2, max(val) as val from atlantis group by g1, g2) tmp where (atlantis.g1, atlantis.g2, atlantis.val) = (tmp.g1,tmp.g2,tmp.val);
    UPDATE 3
    test=*# select * from atlantis ;
     pk | g1 | g2 | val | flag
    ----+----+----+-----+------
      1 |  1 |  1 |  10 | f
      2 |  1 |  1 |  20 | f
      5 |  1 |  4 |  15 | f
      3 |  1 |  1 |  30 | t
      4 |  1 |  2 |  12 | t
      6 |  1 |  4 |  25 | t
    (6 Zeilen)
    
    test=*#
    
    ;-)
     
  7. atlantis

    atlantis Benutzer

    Du bist ja schneller als der Schall:

    Dein Vorschlag:
    update atlantis set flag = true from (select g1, g2, max(val) as val from atlantis group by g1, g2) tmp where
    (atlantis.g1, atlantis.g2, atlantis.val) = (tmp.g1,tmp.g2,tmp.val);

    liefert in Analogie bei 3 Gruppenattributen:

    update T_TRAD_UPE_SB set max =1 from (select Z_TLN, UPE_QUELLE, GUELTIG_AB, max(UPE) as UPE from T_TRAD_UPE_SB group by Z_TLN, UPE_QUELLE, GUELTIG_AB) tmp where
    (T_TRAD_UPE_SB.Z_TLN, T_TRAD_UPE_SB.UPE_QUELLE,T_TRAD_UPE_SB.GUELTIG_AB, T_TRAD_UPE_SB.UPE) = (tmp.Z_TLN,tmp.UPE_QUELLE,tmp.GUELTIG_AB, tmp.UPE);

    Das liefert wiederum:
    SQL-Fehler: ORA-00933: SQL-Befehl wurde nicht korrekt beendet
    00933. 00000 - "SQL command not properly ended"

    Habe ich mich wo vertippt oder geht das bei Oracle nicht?

    Bin jetzt mal ne Stunde weg. Werde danach mal die Tabellen bei mir wie in Deinem Beispiel anlegen, so dass ich Deine SQL Statments direkt kopieren kann...

    Danke so weit.

    Atlantis
     
  8. akretschmer

    akretschmer Datenbank-Guru

    Eines von beiden, vermutlich ;-)
     
  9. atlantis

    atlantis Benutzer

    Habe die Tabelle (ATLANTIS) wie von Dir mal erstellt mit 3 Gruppenwerten
    pk g1 g2 g3 val flag
    1 1 1 1 10 f
    2 1 1 1 20 f
    3 1 1 1 30 f
    4 1 2 2 12 f
    5 4 1 1 15 f
    6 1 4 2 25 f
    7 1 2 2 20 f

    update ATLANTIS
    set FLAG = true
    from
    ( select G1, G2, G3, max(VAL) as VAL from ATLANTIS group by G1, G2, G3) tmp
    where (ATLANTIS.G1, ATLANTIS.G2, ATLANTIS.VAL) =(tmp.G1,tmp.G2,tmp.G3,tmp.VAL);

    ergibt gleiche Fehlermeldung:
    SQL-Fehler: ORA-00933: SQL-Befehl wurde nicht korrekt beendet
    00933. 00000 - "SQL command not properly ended"

    Die Select alleine läuft auch nicht. Gleiche Fehlermeldung...

    Irgendjemand noch eine Idee für Oracle?
    Ich bekomme das Select zum Laufen, wenn der Group by part ganz zum Schluss kommt und die where ATLANTIS.G1 = tmp.G1 mit AND ... einzeln verglichen werden.

    Muss mir morgen mal den Konstrukt set xxx from (select...) nach ein paar Beispielen ergoogeln. Habe noch nicht verstanden, wie das generell funktioniert.

    Dennoch vielen herzlichen Dank bis jetzt!

    Atlantis
     
  10. akretschmer

    akretschmer Datenbank-Guru

    im Where hast auf der einen Seite vom = 3 Spalten und auf der anderen 4.
     
  11. atlantis

    atlantis Benutzer

    hallo akretschmer

    Ich bin erstaunt!
    Du hast echt ein Blick dafür.
    Dieser Fehler war jedoch leider noch nicht die Ursache...
    In meiner Abfrage mit den Originaltabellen hatte es auch gestimmt...

    update ATLANTIS
    set FLAG = true
    from
    ( select G1, G2, G3, max(VAL) as VAL from ATLANTIS group by G1, G2, G3) tmp
    where (ATLANTIS.G1, ATLANTIS.G2, ATLANTIS.G3, ATLANTIS.VAL) =(tmp.G1,tmp.G2,tmp.G3,tmp.VAL);
    liefert immer noch:

    Fehler bei Befehlszeile : 3 Spalte : 1
    Fehlerbericht -
    SQL-Fehler: ORA-00933: SQL-Befehl wurde nicht korrekt beendet
    00933. 00000 - "SQL command not properly ended"

    Jetzt mach ich mich mal dran den Construct
    set .. from (Select...) zu verstehen an ein paar Beispielen...
     
  12. atlantis

    atlantis Benutzer

    hallo akretschmer

    Ich bin erstaunt!
    Du hast echt ein Blick dafür.
    Dieser Fehler war jedoch leider noch nicht die Ursache...
    In meiner Abfrage mit den Originaltabellen hatte es auch gestimmt...

    update ATLANTIS
    set FLAG = true
    from
    ( select G1, G2, G3, max(VAL) as VAL from ATLANTIS group by G1, G2, G3) tmp
    where (ATLANTIS.G1, ATLANTIS.G2, ATLANTIS.G3, ATLANTIS.VAL) =(tmp.G1,tmp.G2,tmp.G3,tmp.VAL);
    liefert immer noch:

    Fehler bei Befehlszeile : 3 Spalte : 1
    Fehlerbericht -
    SQL-Fehler: ORA-00933: SQL-Befehl wurde nicht korrekt beendet
    00933. 00000 - "SQL command not properly ended"

    Jetzt mach ich mich mal dran den Construct
    set .. from (Select...) zu verstehen an ein paar Beispielen...
     
  13. atlantis

    atlantis Benutzer

    hallo akretschmer, hallo alle "Nachleser"

    Habe in einem anderen Forum Hilfe auf mein Problem gefunden. Für alle die ein ähnliches Problem haben,
    so sieht dann eine mögliche Lösung für meine Problemstellung mit der vereinfachten Tabelle von akretschmer aus:

    UPDATE ATLANTIS dst
    SET dst.FLAG = 1
    WHERE
    EXISTS (
    SELECT src.G1, src.G2, MAX(src.VAL) AS max_VAL
    FROM ATLANTIS src
    GROUP BY src.G1, src.G2, src.G3
    HAVING dst.G1 = src.G1 AND dst.G2 = src.G2 AND dst.VAL = MAX(src.VAL)
    )
    G1 G2 G3 VAL FLAG
    1 1 1 10 0
    1 1 1 20 0
    1 1 1 30 1
    1 2 2 12 0
    1 4 2 35 1
    1 4 2 25 0
    1 2 2 20 1


    Wenn zusätzlich auch das FLAG auf 0 gesetzt werden soll hilft:
    BEGIN
    UPDATE ATLANTIS dst
    SET dst.FLAG = CASE WHEN EXISTS
    (
    SELECT src.G1, src.G2, MAX(src.VAL) AS max_VAL
    FROM ATLANTIS src
    GROUP BY src.G1, src.G2, src.G3
    HAVING dst.G1 = src.G1 AND dst.G2 = src.G2 AND dst.VAL = MAX(src.VAL)
    )
    THEN 1
    ELSE 0
    END;

    Nochmals vielen Dank für die Unterstützung.

    Atlantis geht jetz nicht mehr unter ...
     
  14. atlantis

    atlantis Benutzer

    Einen "gelöst" Marker gibt es in diesem Forum hier nicht, oder?
    Würde diesen jetzt auf erledigt setzen...
     
    akretschmer gefällt das.
  15. akretschmer

    akretschmer Datenbank-Guru

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