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

atlantis · 13 Dezember 2016

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

akretschmer · 13 Dezember 2016

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.

atlantis · 14 Dezember 2016

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 !

akretschmer · 14 Dezember 2016

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)

atlantis · 14 Dezember 2016

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

akretschmer · 14 Dezember 2016

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=*#

;-)

atlantis · 14 Dezember 2016

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

akretschmer · 14 Dezember 2016

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

Eines von beiden, vermutlich ;-)

atlantis · 14 Dezember 2016

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

akretschmer · 14 Dezember 2016

atlantis schrieb:
where (ATLANTIS.G1, ATLANTIS.G2, ATLANTIS.VAL) =(tmp.G1,tmp.G2,tmp.G3,tmp.VAL);

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

atlantis · 15 Dezember 2016

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...

atlantis · 19 Dezember 2016

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...

atlantis · 19 Dezember 2016

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 ...

atlantis · 19 Dezember 2016

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

akretschmer · 19 Dezember 2016

Nein.

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

atlantis

Benutzer

akretschmer

Datenbank-Guru

atlantis

Benutzer

akretschmer

Datenbank-Guru

atlantis

Benutzer

akretschmer

Datenbank-Guru

atlantis

Benutzer

akretschmer

Datenbank-Guru

atlantis

Benutzer

akretschmer

Datenbank-Guru

atlantis

Benutzer

atlantis

Benutzer

atlantis

Benutzer

atlantis

Benutzer

akretschmer

Datenbank-Guru