Zeilen bearbeiten mit UPDATE

• » Home
• »Sql-Tutorial
• » Update - Zeilen aktualisieren

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

• Update einer Tabelle ohne Verknüpfung mit anderen Tabellen

  UPDATE <Tabelle>
  	SET <Name einer Spalte> = <Ausdruck aus Spalten,
  		Konstanten, Funktionen>
  		[, weitere Spaltennamen = Ausdruck]
  	WHERE <Bedingung>

• Update einer Tabelle mit JOIN (Ms-Access)

  UPDATE <Tabelle> [INNER | LEFT | RIGHT] JOIN <Tabelle>
  	ON <Spalte-1 = Spalte-2>
  	SET <Name einer Spalte> = <Ausdruck aus Spalten,
  		Konstanten, Funktionen>
  		[, weitere Spaltennamen = Ausdruck]
  	WHERE <Bedingung>

• Update einer Tabelle mit JOIN (ANSI, Ms-SqlServer)

  UPDATE <Tabelle | View>
  	SET <Name einer Spalte> = <Ausdruck aus Spalten,
  		Konstanten, Funktionen>
  		[, weitere Spaltennamen = Ausdruck]
  	[FROM <Tabelle> [INNER | LEFT | RIGHT] JOIN <Tabelle>
  	ON <Spalte-1 = Spalte-2>]
  	WHERE <Bedingung>

• Zunächst wird die zu aktualisierende Tabelle angegeben. Nach SET folgt die Liste der zu aktualisierenden Zellen, für die rechts vom Gleichheitszeichen der neue Wert angegeben wird. Im ANSI-Standard kann anschließend eine JOIN-Verknüpfung folgen, durch welche die von der Aktualisierung betroffenen Zeilen genauer eingeschränkt werden. Schließlich werden mit einer WHERE-Klausel einzelne Zeilen ausgewählt.

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

• Deterministische und nichtdeterministische Anweisungen: Bei UPDATE-Befehlen mit JOIN-Klausel muß sichergestellt sein, daß der JOIN zu jeder zu aktualisierenden Zeile nur einen Ausdruck liefert. Ist dies der Fall, so spricht man von deterministischen Anweisungen. Ansonsten handelt es sich um eine nichtdeterministische Anweisung, deren Ergebnis nicht eindeutig definiert ist. Denn in diesem Fall ist nicht vorhersehbar, welcher Ausdruck tatsächlich in die Zelle eingetragen wird. Dieses Problem taucht hauptsächlich dann auf, wenn eine Spalte in der Grundtabelle aktualisiert werden soll und im JOIN eine Detailtabelle herangezogen wird, so daß in der Detailtabelle kein, ein oder mehrere Datensätze für die Grundzeile zur Verfügung stehen.

   

  Ms-Access:

  UPDATE ARTIKEL As A INNER JOIN UMSATZ AS U
  	On A.A_NR = U.A_NR
  	SET A.Gesamt = A.A_PREIS * U.A_STUECK

  Ms-SqlServer:

  UPDATE ARTIKEL
  	SET Gesamt = U.A_STUECK * A.A_PREIS
  	FROM UMSATZ AS U INNER JOIN ARTIKEL As A
  	On U.A_NR = A.A_NR

  Dies ist fast dasselbe Beispiel wie oben (6), nur wird nun die Spalte ARTIKEL.Gesamt aktualisiert. Da zu einem Artikel mehrere Einzelumsätze existieren, ist nicht bekannt, welcher Einzelumsatz zur Berechnung ausgewählt wird.
• Anmerkungen zur Syntax: Bei Ms-Access kann nur ein Ausdruck aktualisiert werden, der eine gänzlich aktualisierbare Tabelle darstellt. Diese kann real oder virtuell, als JOIN deklariert sein, so daß praktisch mehrere Tabellen neue Einträge erhalten. So funktioniert die folgende Anweisung in Ms-Access:

  UPDATE ARTIKEL As A INNER JOIN UMSATZ As U
  	On A.A_NR = U.A_NR
  	SET A.Gesamt = Null,
  		U.Gesamt = A.A_PREIS * U.A_STUECK

  Gemäß dem ANSI-Standard ist jedoch nur die Aktualisierung eines einzigen Objektes möglich. Bei diesem kann es sich allerdings um einen mehrere Tabellen umfassenden View handeln, als Objektbezeichner darf auf die UPDATE-Anweisung jedoch nur ein Ausdruck folgen. Für die in Ms-Access funktionierende obige Anweisung, die eine temporäre Sicht erstellt, wäre in Ms-SqlServer zunächst ein eigenständiger View zu erstellen, dieser könnte genutzt werden.
  Eine ernstzunehmende Einschränkung bei Ms-Access ist die Unmöglichkeit, zwischen SET und WHERE einen JOIN festzulegen, mit welchem entweder gewisse Zeilen zur Aktualisierung ausgewählt werden oder mit welchem zusätzliche Spalten zur Berechnung des neuen Wertes herangezogen werden können.

   

  Bei Ms-SqlServer ist zu beachten, daß das Objekt, welches upgedated werden soll, ohne Alias-Namen verwendet werden muß. Ferner darf im folgenden JOIN-Abschnitt dieses Objekt höchstens einmal aufgeführt werden, hier sind Alias-Namen erlaubt und wünschenswert.
• Die Technik, Zellen mittels Funktionen in atomare Werte zu zerlegen, wird in vielfältiger Weise benötigt, falls schwach strukturierte Daten in eine Datenbank eingelesen werden sollen. Im Regelfall ist es das beste, die Daten zunächst in eine einzige Textspalte einzulesen und diese Spalte anschließend mit Datenbank-Techniken schrittweise zu zerlegen. Ein zeilenweises Zerlegen vor dem Einlesen in die Datenbank dürfte in den meisten Fällen unter Performance-Gesichtspunkten schlechter sein, da die Leistung eines DBMS gerade darin besteht, eine Menge vieler Zeilen auf einmal zu verarbeiten. Eventuelle Ein- bzw. Ausschließungskriterien (etwa ein fehlendes Komma zwischen Nachname und Vorname) können über die WHERE-Bedingung ausgeschlossen werden. Die Funktionen sind leider in den verschiedenen DBMS höchst unterschiedlich implementiert, so daß für ein konkretes Problem die jeweilige Dokumentation zu Rate gezogen werden muß.