Zeilen bearbeiten mit UPDATE

Vorbemerkung

Der Update-Befehl nimmt unter den Sql-Anweisungen insofern eine Sonderposition ein, da sich die Syntax bei Verwenden einer JOIN-Klausel zwischen Ms-Access und Ms-SqlServer unterscheidet. Damit die Beispiele mit dem Sql-interaktiv-lernen getestet werden können, werden in diesen Fällen beide Versionen angegeben.

Syntax

Beispiele

Fügen Sie zu den beiden Tabellen ARTIKEL und UMSATZ mit dem folgenden Befehl eine neue Spalte 'Gesamt' hinzu:
ALTER TABLE ARTIKEL ADD Gesamt money
ALTER TABLE UMSATZ ADD Gesamt money
Diese Spalten können zu Testzwecken beliebig manipuliert werden, ohne daß sich die Grunddaten deshalb ändern.
  1. UPDATE ARTIKEL
    	SET Gesamt = 0
    Einfachste Version einer Update-Anweisung: Eine Spalte wird mit einem neuen Wert, hier mit der Konstanten 0 belegt, die Aktualisierung wird für alle Zeilen ausgeführt.
  2. UPDATE ARTIKEL
    	SET Gesamt = A_PREIS
    Jeder Zelle 'Gesamt' ist die Zelle 'A_PREIS' derselben Zeile zugeordnet. Also wird der Wert der letzteren in die Zelle 'Gesamt' kopiert.
  3. UPDATE ARTIKEL
    	SET Gesamt = A_PREIS * 1.19
    Dasselbe wie zuvor, nun ergänzt um die Multiplikation mit einer Konstanten. Eine solche Spalte mag bsp. den Bruttopreis eines Artikels enthalten.
  4. UPDATE ARTIKEL
    	SET Gesamt = A_PREIS * 1.19
    	WHERE A_PREIS > 100
    Nun wird die Menge der zu aktualisierenden Zeilen eingeschränkt, auf daß nicht alle, sondern nur jene Zeilen aktualisiert werden, deren Preis vor der Aktualisierung größer 100 ist.
  5. Erstellung der gespeicherten Prozedur:
    CREATE PROCEDURE up_upd_Artikel
    	@A_Nr int,
    	@A_Name nvarchar(50),
    	@A_Preis money
    As
    	UPDATE ARTIKEL
    		SET A_NAME = @A_Name,
    			A_PREIS = @A_Preis
    	WHERE A_Nr = @A_Nr
    Ausführung:
    Execute up_upd_Artikel 11, 'Oberhemd (Doppelpackung)', 59.90
    Dies ist ein kanonisches Beispiel für eine gespeicherte Prozedur, welche den Datensatz mit der übergebenen @A_Nr aktualisiert. Für die Ms-Access-Version über .NET sind die Parameter zu entfernen.
  6. Ms-Access:
    UPDATE ARTIKEL As A INNER JOIN UMSATZ AS U
    	On A.A_NR = U.A_NR
    	SET U.Gesamt = A.A_PREIS * U.A_STUECK
    Ms-SqlServer:
    UPDATE UMSATZ
    	SET Gesamt = U.A_STUECK * A.A_PREIS
    	FROM UMSATZ AS U INNER JOIN ARTIKEL As A
    	On U.A_NR = A.A_NR
    Diese Anweisung berechnet in der Tabelle 'Umsatz' den Wert jeder einzelnen Zeile, indem der zum Artikel gehörende Preis mit 'A_Stueck' multipliziert wird. Da es zu jeder Umsatz-Zeile genau eine Zeile in der Tabelle 'Artikel' gibt, ist diese Anweisung eindeutig.
  7. Aktualisieren der Spalte ARTIKEL.Gesamt mit dem Produkt aus ARTIKEL.A_PREIS und der Summe aller UMSATZ.A_STUECK für diesen Artikel.
    Zur Lösung dieser Aufgabe scheint es zunächst zu genügen, den Aktualisierungsausdruck
    SET A.Gesamt = A.A_PREIS * SUM(U.A_STUECK)
    zu verwenden, für Ms-SqlServer würde man das Präfix bei 'A.Gesamt' entfernen. Eine solche Abfrage läßt sich mit Ms-Access sogar speichern, jedoch nicht ausführen. Es wird die von Gruppierungsabfragen bekannte Fehlermeldung "Sie wollten eine Abfrage ausführen, die den angegebenen Ausdruck 'Gesamt' nicht als Teil der Aggregatfunktion einschließt" ausgibt (vgl. Mit GROUP BY Daten aggregieren und auswerten - Bemerkungen). Jeder Versuch, die Update-Anweisung um eine Group-By-Klausel zu ergänzen, scheitert jedoch ebenfalls. Beim Ms-SqlServer ist die Fehlermeldung genauer: Ein Aggregat kann nicht in der SET-Liste einer UPDATE-Anweisung auftreten. Diese Aufgabe kann deshalb nur mit einer eigenständigen Unterabfrage gelöst werden, welche für jeden Artikel bereits die Summation über die diesem Artikel zugeordneten A_Stueck enthält. Tatsächlich gelingt dies nur beim Ms-SqlServer. Bei Ms-Access wird nur eine einzige virtuelle Tabelle als das zu aktualisierende Recordset betrachtet. Die Hilfe für Office XP (Access, Abschnitt Microsoft JET-SQL-Referenz) erwähnt die Möglichkeit eines JOIN nicht einmal. Diese virtuelle Tabelle muß vollständig aktualisierbar sein, eine Abfrage, welche Aggregatfunktionen verwendet und gruppiert, ist jedoch schreibgeschützt. Deshalb kann nur eine Lösung für Ms-SqlServer angegeben werden:
    UPDATE ARTIKEL
    	SET Gesamt = A.A_PREIS * U1.A_Stueck_Gesamt
    	FROM ARTIKEL As A INNER JOIN
    		(SELECT B.A_NR, SUM(B.A_STUECK
    		FROM UMSATZ As B
    		GROUP BY B.A_NR) As U1
    	On A.A_NR = B.A_NR
    Für Ms-Access müßte die Unterabfrage als eigenständige SELECT-Anweisung in eine temporäre Tabelle kopiert, über diese die Verknüpfungs-Aktualisierung durchgeführt und die temporäre Tabelle am Schluß gelöscht werden. Dies gilt analog für DBMS, welche Unterabfragen nicht unterstützen.
  8. Zerlegung von Vertreter.V_Name in Vorname und Nachname:
    Offenkundig ist die Tabelle VERTRETER nicht normalisiert. So sind Nachname und Vorname in einer Spalte zusammengefaßt, dies verletzt die Forderung nach atomaren Attributen. Als Folge kann bsp. eine Suche nach einem Nachnamen nicht einfach diesen angeben und mit dem Gleichheitsoperator (=) überprüfen, sondern es muß eine Anweisung der Form
    A_NAME LIKE 'Meyer,%'
    verwendet werden. Mittels der Update-Anweisung und unter Verwendung von Funktionen können solche Spalten in ihre Bestandteile zerlegt werden.
    ALTER TABLE VERTRETER ADD Nachname nvarchar(50)
    ALTER TABLE VERTRETER ADD Vorname nvarchar(50)
    Dies fügt zwei neue Spalten 'Nachname' und 'Vorname' ein.

    Ms-Access:
    UPDATE VERTRETER
    	SET Nachname = Left(V_NAME, InStr(VERTRETER, ',') - 1),
    		Vorname = Trim(Mid(VERTRETER,
    			InStr(VERTRETER, ',') + 1))
    Ms-SqlServer:
    UPDATE VERTRETER
    	SET Nachname = Left(V_NAME, CharIndex(',', V_NAME) - 1),
    		Vorname = LTrim(SubString(V_NAME,
    			CharIndex(',', V_NAME) + 1))
  9. Berechnung des höchsten Einzelumsatzes pro Artikel, eingetragen in der Tabelle UMSATZ (Ms-SqlServer):
    UPDATE UMSATZ SET Gesamt = Null
    
    UPDATE UMSATZ
    	SET Gesamt = A.A_PREIS * U.A_STUECK
    FROM ARTIKEL As A INNER JOIN UMSATZ As U
    On A.A_NR = U.A_NR
    WHERE A.A_PREIS * U.A_STUECK =
    	(SELECT MAX(A.A_PREIS * U.A_STUECK
    	FROM ARTIKEL As A INNER JOIN UMSATZ As V
    	On B.A_NR = V.A_NR
    	WHERE B.A_NR = A.A_NR)
    Diese Anweisung setzt zunächst alle Einträge in UMSATZ.Gesamt zurück und trägt nur in jene Zeilen den Gesamtumsatz ein, die für diesen Artikel den höchsten Einzelumsatz darstellen. Hier wird sowohl ein JOIN als auch eine korrelierte Unterabfrage genutzt, die sich allerdings in einen JOIN mit Unterabfrage auflösen läßt:
    UPDATE UMSATZ
    	SET Gesamt = A.A_PREIS * U.A_STUECK
    FROM ARTIKEL As A INNER JOIN UMSATZ As U
    On A.A_NR = U.A_NR INNER JOIN
    	(SELECT B.A_NR, MAX(B.A_PREIS * U1.A_STUECK) As Maximum
    	FROM ARTIKEL As B INNER JOIN UMSATZ As U1
    	On B.A_NR = U1.A_NR
    	GROUP BY B.A_NR) As C
    On U.A_NR = C.A_NR AND
    	A.A_PREIS * U.A_STUECK= C.Maximum
    Diese Anweisung ist ein Beispiel dafür, wie eine JOIN-Klausel dafür genutzt werden kann, wenige Zeilen aus der zu aktualisierenden Tabelle auszuwählen. Aktualisiert wird die Tabelle UMSATZ, das Hinzunehmen von ARTIKEL reduziert die Zahl der Zeilen nicht. Erst die zusätzliche JOIN-Klausel, die pro Artikel nur noch eine Zeile enthält, vermindert die Zahl der vom UPDATE betroffenen Zeilen auf vier.

Bemerkungen


© 2003-2018 Jürgen Auer, Berlin.