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.
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.
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.
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.
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.
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
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.
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.
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.
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:
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ß.
Link zur hiesigen Seite als QR-Code
Kontaktformular:
Schreiben Sie mir und wir bauen gemeinsam Ihre neue Web-Datenbank!
Mit dem Klick auf den Button stimmen Sie zu, daß Cookies in Ihrem Browser gespeichert werden. Informationen zu den gespeicherten Cookies finden Sie unter Datenschutz#Cookies.Bei Fragen zur Technik wenden Sie sich bitte an Server-Daten - Web-Datenbank-Lösungen