Update mit Case? Wenn ein Wert nicht vorhanden ist, dann...

believn · 15 September 2016

Hallo Zusammen,

ich habe eine Tabelle mit verschiedenen Kundennummern. Wenn ich Kunden selektiere möchte ich immer eine der Kundennummern haben (Typ 9) Manche Kunden haben aber keine Typ 9 Kundennummer, aber eine Typ 4.

Bisher löse ich das wie folgt:

Code:

bisherige Vorgehensweise

UPDATE    ausgangstabellle_001

SET        Kundennummer = Wert

FROM    ausgangstabellle_001 AS X
            INNER JOIN tab_001 AS Y        ON X.KUNDEN_ID = Y.KUNDEN_ID

WHERE    Typ = 4
        AND VALIDTO >= CAST(GETDATE() AS DATE) ;


UPDATE    ausgangstabellle_001

SET        Kundennummer = Wert

FROM    ausgangstabellle_001 AS X
            INNER JOIN tab_001 AS Y        ON    X.KUNDEN_ID = Y.KUNDEN_ID

WHERE    Typ = 9
        AND VALIDTO >= CAST(GETDATE() AS DATE) ;

Ich hätte das aber gern in einer Abfrage. Also quasi vereinfacht dargestellt:

Nimm bitte die Typ 9 Kundennummer, wenn die nicht da ist halt die Typ 4

Hier nochmal in Tabellenform, wie die Tabellen ungefähr aussehen.

akretschmer · 16 September 2016

Gut. Fangen wir mal langsam an. Du hast:

Code:

test=*# select * from tab001 ;
 kunden_id |  wert  | typ
-----------+-----------+-----
  5 | U12345678 |  9
  5 | 012345678 |  4
  6 | U12345678 |  10
  6 | 012345678 |  4
(4 Zeilen)

Und willst je kunden_id den 'wert', und zwar bevorzugt wenn typ=9 ist, ansonsten wenn typ=4 ist, ja?

Code:

test=*# with typ_9 as (select * from tab001 where typ = 9), typ_4 as (select * from tab001 where kunden_id not in (select kunden_id from typ_9) and typ = 4) select * from typ_9 union all select * from typ_4;
 kunden_id |  wert  | typ
-----------+-----------+-----
  5 | U12345678 |  9
  6 | 012345678 |  4
(2 Zeilen)

PostgreSL, das mit den WITH-Anfragen kann aber M$SQL soweit mir bekannt auch. Das als Basis für das Update zu verwenden überlasse ich Dir zur Übung.

ukulele · 16 September 2016

Gibt mehrere Möglichkeiten, die hier sollte in jedem Fall gehen:

Code:

UPDATE   ausgangstabellle_001
SET     Kundennummer = (CASE WHEN Y1.P_ID IS NULL THEN Y2.Wert ELSE Y1.Wert END)
FROM   ausgangstabellle_001 AS X
LEFT JOIN tab_001 AS Y1
ON     X.KUNDEN_ID = Y1.KUNDEN_ID
AND     Y1.Typ = 9
LEFT JOIN tab_001 AS Y2
ON     X.KUNDEN_ID = Y2.KUNDEN_ID
AND     Y2.Typ = 9
WHERE   VALIDTO >= CAST(GETDATE() AS DATE) ;

believn · 16 September 2016

Also es gibt Kunden, die haben eine neue Kundennummer (Typ = 9) und eine alte Kundennummer (Typ = 4). Es gibt aber auch Kunden, die haben noch keine neue Kundennummer, also nur Typ = 4.

In meinem Beispiel hat Kunde 5 eine alte und neue Kundennummer (4 und 9) - wenn ich da Auswertungen mache, möchte ich das Typ 9 angezeigt wird.
Kunde 6 hat nur eine alte Kundennumer (Typ = 4). Statt NULL oder ein leeres Feld, nehme ich dann auch die alte Kundennummer.

Ich habe beide Vorschläge getestet, aber das gewünschte Ergebnis nicht erzielt. Ich werde auch gerade erst in SQL angelernt und die erste Schulung folgt im Oktober...

Code:

Ich versuche das nochmal genauer darzustellen:

Tabellenname = KDNR_003

Spalten
WERT          |  Typ   |  KDNR              |Kunden_ID
0209123009  |    4     |    (leer)             |  1
5211488009  |    4     |    (leer)             |  2
B784137759  |    9     |    (leer)             |  2

Mit dem Update soll das Feld KDNR befüllt werden

Das gewünschte Ergebnis soll dann so aussehen:

Code:

Tabellenname = KDNR_003

Spalten
WERT        |  Typ   |  KDNR              |Kunden_ID
0209123009  |    4     |    0209123009            |  1
5211488009  |    4      |    B784137759             |  2
B784137759  |    9      |     B784137759             |  2

akretschmer · 16 September 2016

so also?

Code:

test=*# select * from believn ;
  wert  | typ | kdnr | kunden_id
------------+-----+------+-----------
 0209123009 |  4 |  |  1
 5211488009 |  4 |  |  2
 B784137759 |  9 |  |  2
(3 Zeilen)

test=*# with new as (with typ_9 as (select * from believn where typ = 9), typ_4 as (select * from believn where kunden_id not in (select kunden_id from typ_9) and typ = 4) select * from typ_9 union all select * from typ_4) update believn set kdnr = new.wert from new where believn.kunden_id = new.kunden_id;
UPDATE 3
test=*# select * from believn ;
  wert  | typ |  kdnr  | kunden_id
------------+-----+------------+-----------
 0209123009 |  4 | 0209123009 |  1
 5211488009 |  4 | B784137759 |  2
 B784137759 |  9 | B784137759 |  2
(3 Zeilen)

test=*#

believn · 16 September 2016

Habe das mal nun auf meine Tabelle KDNR_003 umgeschrieben, aber da kommen nur diverse Fehlereldungen.

Code:

with new as (with typ_9 as (select * from KDNR_003 where typ = 9), typ_4 as (select * from KDNR_003 where kunden_id not in (select kunden_id from typ_9) and typ = 4) select * from typ_9 union all select * from typ_4) update KDNR_003 set kdnr = new.wert from new where KDNR_003.kunden_id = new.kunden_id ;

Meldung 156, Ebene 15, Status 1, Zeile 164

Falsche Syntax in der Nähe des with-Schlüsselworts.

Meldung 319, Ebene 15, Status 1, Zeile 164

Falsche Syntax in der Nähe des WITH-Schlüsselworts. Falls diese Anweisung ein allgemeiner Tabellenausdruck, eine XMLNAMESPACES-Klausel oder eine CHANGE TRACKING CONTEXT-Klausel ist, muss die vorherige Anweisung mit einem Semikolon abgeschlossen werden.

Meldung 102, Ebene 15, Status 1, Zeile 164

Falsche Syntax in der Nähe von ','.

Meldung 102, Ebene 15, Status 1, Zeile 164

Falsche Syntax in der Nähe von ')'.

akretschmer · 16 September 2016

believn schrieb:
aber da kommen nur diverse Fehlereldungen

Tut mir Leid. Offenbar unterstützt M$SQL das nicht. Vielleicht kann ja @ukulele das passend umschreiben ;-)

ukulele · 16 September 2016

Also 2 mal WITH hintereinander geht bei MSSQL nicht, man kann aber im Kontext einer Tabelle auf die vorhergehende Tabelle zugreifen. Ich sehe da also nicht wirklich eine Notwendigkeit WITH zu schachteln.

Was geht denn an meiner Abfrage eigentlich nicht?

Code:

UPDATE ausgangstabellle_001
SET ausgangstabellle_001.Kundennummer = (CASE WHEN Y1.P_ID IS NULL THEN Y2.Wert ELSE Y1.Wert END)
FROM ausgangstabellle_001
LEFT JOIN tab_001 AS Y1
ON ausgangstabellle_001.KUNDEN_ID = Y1.KUNDEN_ID
AND Y1.Typ = 9
LEFT JOIN tab_001 AS Y2
ON ausgangstabellle_001.KUNDEN_ID = Y2.KUNDEN_ID
AND Y2.Typ = 9
WHERE ausgangstabellle_001.VALIDTO >= CAST(GETDATE() AS DATE) ;

bzw. zum testen

Code:

SELECT ausgangstabellle_001.*,
(CASE WHEN Y1.P_ID IS NULL THEN Y2.Wert ELSE Y1.Wert END) AS test
FROM ausgangstabellle_001
LEFT JOIN tab_001 AS Y1
ON ausgangstabellle_001.KUNDEN_ID = Y1.KUNDEN_ID
AND Y1.Typ = 9
LEFT JOIN tab_001 AS Y2
ON ausgangstabellle_001.KUNDEN_ID = Y2.KUNDEN_ID
AND Y2.Typ = 9
WHERE ausgangstabellle_001.VALIDTO >= CAST(GETDATE() AS DATE) ;

Ansonsten kann ich das nachher mal in WITH umbauen.

akretschmer · 16 September 2016

das Problem könnte sein, daß im Update man nicht auf eine via WITH erzeugte Tabelle zugreifen kann, da bin ich mir nicht sicher, das ist (möglicherweise) ein spezielles Feature von PG.

ukulele · 16 September 2016

Doch das geht auch bei MSSQL wenn ich nicht irre. Ich finde das mit zwei Joins und einem CASE jetzt aber irgendwie simpler.

ukulele · 16 September 2016

Dein Code wäre in etwa so übersetzt:

Code:

WITH t AS (
SELECT   kunden_id,
     wert
FROM   KDNR_003
WHERE   typ = 9
UNION ALL
SELECT   kunden_id,
     wert
FROM   KDNR_003
WHERE   typ = 4
AND     kunden_id NOT IN ( SELECT kunden_id FROM KDNR_003 WHERE typ = 9 )
)
UPDATE   KDNR_003
SET     KDNR_003 = t.wert
FROM   t
WHERE   KDNR_003.kunden_id = t.kunden_id

Ich habe mir erlaubt ein bischen was raus zu kürzen.
Ich würde sogar noch etwas weiter gehen mit WITH:

Code:

WITH t AS (
SELECT   ROW_NUMBER() OVER (PARTITION BY kunden_id ORDER BY typ DESC,wert) AS zeile,
     kunden_id,
     wert
FROM   KDNR_003
WHERE   typ IN ( 9,4)
)
UPDATE   KDNR_003
SET     KDNR_003 = t.wert
FROM   t
WHERE   KDNR_003.kunden_id = t.kunden_id
AND     t.zeile = 1

Es wäre auch nicht auszuschließen das es pro kunden_id mehrere typ=9 Einträge mit unterschiedlichen wert gibt. In diesem Fall würde der kleinste genommen, in den anderen Beispielen würde der Eintrag glaube ich mehrfach gesetzt und in beliebiger Reihenfolge.

believn · 16 September 2016

OK ich erkenne, dass meine Darstellung nicht sauber ist und es daher dann bei mir nicht richtig klappt... Sorry!

Code:

tab01 (die Ausgangstabelle auf die geupdated werden soll)

Kunden_ID      |   KDNR
1             |  (leer)
2             |  (leer)

Code:

tab02 (die mit den Kundennummern)

Kunden_ID   |    Typ     | Wert
1            |    4      | 0209123009
2            |    4      | 5211488009
2            |    9      | B784137759

SQL Günter - Ende

ukulele · 16 September 2016

Code:

CREATE TABLE tab01(
   Kunden_ID INT NOT NULL,
   KDNR VARCHAR(20) NULL
   );
INSERT INTO tab01(Kunden_ID) VALUES(1),(2);

CREATE TABLE tab02(
   Kunden_ID INT NOT NULL,
   Typ SMALLINT NOT NULL,
   Wert VARCHAR(20) NOT NULL
   )
INSERT INTO tab02(Kunden_ID,Typ,Wert) VALUES(1,4,'0209123009'),(2,4,'5211488009'),(2,9,'B784137759');

SELECT   *
FROM   tab01;
SELECT   *
FROM   tab02;

Kunden_ID  KDNR
----------- --------------------
1  NULL
2  NULL

(2 Zeile(n) betroffen)

Kunden_ID  Typ  Wert
----------- ------ --------------------
1  4  0209123009
2  4  5211488009
2  9  B784137759

(3 Zeile(n) betroffen)

UPDATE   tab01
SET     tab01.KDNR = (CASE WHEN Y1.Kunden_ID IS NULL THEN Y2.Wert ELSE Y1.Wert END)
FROM   tab01
LEFT JOIN tab02 AS Y1
ON     tab01.Kunden_ID = Y1.Kunden_ID
AND     Y1.Typ = 9
LEFT JOIN tab02 AS Y2
ON     tab01.Kunden_ID = Y2.Kunden_ID
AND     Y2.Typ = 4
WHERE   tab01.KDNR IS NULL

SELECT   *
FROM   tab01;

(2 Zeile(n) betroffen)
Kunden_ID  KDNR
----------- --------------------
1  0209123009
2  B784137759

(2 Zeile(n) betroffen)

Ich hab vorher wohl einmal typ=9 statt typ=4 im Join, Flüchtigkeitsfehler.

believn · 16 September 2016

Hey das klappt nun! Danke.

ukulele · 16 September 2016

Die Lösung mit WITH und ROW_NUMBER() funktioniert aber auch, ist vielleicht etwas eleganter und dynamischer:

Code:

DROP TABLE tab01;
CREATE TABLE tab01(
   Kunden_ID INT NOT NULL,
   KDNR VARCHAR(20) NULL
   );
INSERT INTO tab01(Kunden_ID) VALUES(1),(2);

DROP TABLE tab02;
CREATE TABLE tab02(
   Kunden_ID INT NOT NULL,
   Typ SMALLINT NOT NULL,
   Wert VARCHAR(20) NOT NULL
   )
INSERT INTO tab02(Kunden_ID,Typ,Wert) VALUES(1,4,'0209123009'),(2,4,'5211488009'),(2,9,'B784137759');

SELECT   *
FROM   tab01;
SELECT   *
FROM   tab02;

Kunden_ID  KDNR
----------- --------------------
1  NULL
2  NULL

(2 Zeile(n) betroffen)

Kunden_ID  Typ  Wert
----------- ------ --------------------
1  4  0209123009
2  4  5211488009
2  9  B784137759

(3 Zeile(n) betroffen)

WITH t AS (
SELECT   ROW_NUMBER() OVER (PARTITION BY kunden_id ORDER BY typ DESC,wert) AS zeile,
     kunden_id,
     wert
FROM   tab02
WHERE   typ IN ( 9,4 )
)
UPDATE   tab01
SET     tab01.KDNR = t.Wert
FROM   t
WHERE   tab01.kunden_id = t.Kunden_ID
AND     t.zeile = 1

SELECT   *
FROM   tab01;

(2 Zeile(n) betroffen)
Kunden_ID  KDNR
----------- --------------------
1  0209123009
2  B784137759

(2 Zeile(n) betroffen)

Update mit Case? Wenn ein Wert nicht vorhanden ist, dann...

believn

Benutzer

akretschmer

Datenbank-Guru

ukulele

Datenbank-Guru

believn

Benutzer

akretschmer

Datenbank-Guru

believn

Benutzer

akretschmer

Datenbank-Guru

ukulele

Datenbank-Guru

akretschmer

Datenbank-Guru

ukulele

Datenbank-Guru

ukulele

Datenbank-Guru

believn

Benutzer

ukulele

Datenbank-Guru

believn

Benutzer

ukulele

Datenbank-Guru

Ähnliche Themen