Kapitel 4. Einfügen, Aktualisieren und Löschen
Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: translation-feedback@oreilly.com
In den letzten Kapiteln ging es um grundlegende Abfragetechniken, die sich alle um die Aufgabe drehten, Daten aus einer Datenbank abzurufen. Dieses Kapitel dreht den Spieß um und konzentriert sich auf die folgenden drei Themenbereiche:
-
Neue Datensätze in deine Datenbank einfügen
-
Vorhandene Datensätze aktualisieren
-
Löschen von Datensätzen, die du nicht mehr willst
Damit du sie leichter findest, wenn du sie brauchst, sind die Rezepte in diesem Kapitel nach Themen gruppiert: Alle Einfüge-Rezepte stehen an erster Stelle, gefolgt von den Update-Rezepten und schließlich den Rezepten zum Löschen von Daten.
Das Einfügen ist in der Regel eine unkomplizierte Aufgabe. Sie beginnt mit dem einfachen Problem, eine einzelne Zeile einzufügen. In vielen Fällen ist es jedoch effizienter, neue Zeilen auf Basis von Mengen zu erstellen. Zu diesem Zweck findest du auch Techniken, mit denen du viele Zeilen auf einmal einfügen kannst.
Auch das Aktualisieren und Löschen von Datensätzen beginnt als einfache Aufgabe. Du kannst einen Datensatz aktualisieren und einen Datensatz löschen. Aber du kannst auch ganze Sätze von Datensätzen auf einmal aktualisieren, und zwar auf sehr leistungsfähige Weise. Und es gibt viele praktische Möglichkeiten, Datensätze zu löschen. Du kannst zum Beispiel Zeilen in einer Tabelle löschen, je nachdem, ob sie in einer anderen Tabelle vorhanden sind.
SQL hat sogar eine Möglichkeit, eine relativ neue Ergänzung des Standards, mit der du Daten auf einmal einfügen, aktualisieren und löschen kannst. Das hört sich jetzt vielleicht nicht besonders nützlich an, aber die MERGE-Anweisung ist eine leistungsstarke Methode, um eine Datenbanktabelle mit einer externen Datenquelle zu synchronisieren (z. B. mit einem Flat File Feed von einem entfernten System). Weitere Informationen findest du in Rezept 4.11 in diesem Kapitel.
4.1 Einen neuen Datensatz einfügen
Lösung
Verwende die INSERT-Anweisung mit der VALUES-Klausel, um eine Zeile nach der anderen einzufügen:
insert into dept (deptno,dname,loc) values (50,'PROGRAMMING','BALTIMORE')
Bei DB2, SQL Server, PostgreSQL und MySQL hast du die Möglichkeit, eine Zeile auf einmal oder mehrere Zeilen auf einmal einzufügen, indem du mehrere VALUES Listen einfügst:
/* multi row insert */
insert into dept (deptno,dname,loc)
values (1,'A','B'),
(2,'B','C')
Diskussion
Mit der INSERT-Anweisung kannst du neue Zeilen in Datenbanktabellen erstellen. Die Syntax für das Einfügen einer einzelnen Zeile ist für alle Datenbankmarken gleich.
Als Abkürzung kannst du die Spaltenliste in einer INSERT-Anweisung weglassen:
insert into dept values (50,'PROGRAMMING','BALTIMORE')
Wenn du deine Zielspalten jedoch nicht auflistest, musst du in alle Spalten der Tabelle einfügen und auf die Reihenfolge der Werte in der VALUES-Liste achten; du musst die Werte in der gleichen Reihenfolge angeben, in der die Datenbank die Spalten als Antwort auf eine SELECT *-Abfrage anzeigt. In jedem Fall solltest du auf die Spaltenbeschränkungen achten, denn wenn du nicht in jede Spalte einfügst, erzeugst du eine Zeile, in der einige Werte null sind. Das kann zu einem Fehler führen, wenn es Spalten gibt, die keine Nullen zulassen.
4.2 Einfügen von Standardwerten
Problem
Eine Tabelle kann so definiert werden, dass sie Standardwerte für bestimmte Spalten annimmt. Du möchtest eine Zeile mit Standardwerten einfügen, ohne dass du diese Werte angeben musst.
Betrachte die folgende Tabelle:
create table D (id integer default 0)
Du willst eine Null einfügen, ohne sie explizit in der Werteliste einer INSERT-Anweisung anzugeben. Du willst explizit den Standardwert einfügen, was auch immer dieser Standardwert ist.
Lösung
Alle Marken unterstützen die Verwendung des DEFAULT-Schlüsselworts als Möglichkeit, den Standardwert für eine Spalte explizit festzulegen. Einige Marken bieten zusätzliche Möglichkeiten, das Problem zu lösen.
Das folgende Beispiel veranschaulicht die Verwendung des Schlüsselworts DEFAULT:
insert into D values (default)
Du kannst auch explizit den Spaltennamen angeben, was du immer dann tun musst, wenn du nicht in alle Spalten einer Tabelle einfügen willst:
insert into D (id) values (default)
Oracle8i Database und frühere Versionen unterstützen das Schlüsselwort DEFAULT nicht. Vor Oracle9i Database gab es keine Möglichkeit, explizit einen Standardspaltenwert einzufügen.
MySQL erlaubt es dir, eine leere Werteliste anzugeben, wenn für alle Spalten ein Standardwert definiert ist:
insert into D values ()
In diesem Fall werden alle Spalten auf ihre Standardwerte gesetzt.
PostgreSQL und SQL Server unterstützen eine DEFAULT VALUES Klausel:
insert into D default values
Die DEFAULT VALUES-Klausel bewirkt, dass alle Spalten ihre Standardwerte annehmen.
Diskussion
Das Schlüsselwort DEFAULT in der Werteliste fügt den Wert ein, der bei der Tabellenerstellung als Standardwert für eine bestimmte Spalte angegeben wurde. Das Schlüsselwort ist für alle DBMS verfügbar.
MySQL-, PostgreSQL- und SQL Server-Benutzer haben eine weitere Möglichkeit, wenn alle Spalten in der Tabelle mit einem Standardwert definiert sind (wie in diesem Fall die Tabelle D). Du kannst eine leere VALUES-Liste verwenden (MySQL) oder die DEFAULT VALUES-Klausel angeben (PostgreSQL und SQL Server), um eine neue Zeile mit allen Standardwerten zu erstellen; andernfalls musst du DEFAULT für jede Spalte in der Tabelle angeben.
Bei Tabellen mit einer Mischung aus Standard- und Nicht-Standardspalten ist das Einfügen von Standardwerten für eine Spalte so einfach wie das Ausschließen der Spalte aus der Einfügeliste; du musst das Schlüsselwort DEFAULT nicht verwenden. Angenommen, Tabelle D hat eine zusätzliche Spalte, die nicht mit einem Standardwert definiert wurde:
create table D (id integer default 0, foo varchar(10))
Du kannst einen Standard für ID einfügen, indem du nur FOO in der Einfügeliste aufführst:
insert into D (name) values ('Bar')
Diese Anweisung führt zu einer Zeile, in der ID gleich 0 und FOO gleich BAR ist. ID nimmt den Standardwert an, da kein anderer Wert angegeben wurde.
4.3 Überschreiben eines Standardwerts mit NULL
Lösung
Du kannst explizit NULL in deiner Werteliste angeben:
insert into d (id, foo) values (null, 'Brighten')
Diskussion
Nicht jeder weiß, dass du in der Werteliste einer INSERT-Anweisung explizit NULL angeben kannst. Wenn du keinen Wert für eine Spalte angeben willst, lässt du diese Spalte normalerweise in der Spalten- und Werteliste weg:
insert into d (foo) values ('Brighten')
Hier ist kein Wert für ID angegeben. Viele würden erwarten, dass die Spalte den Wert Null annimmt, aber leider wurde bei der Erstellung der Tabelle ein Standardwert angegeben, so dass das Ergebnis der vorangegangenen INSERT ist, dass ID den Wert Null (den Standardwert) annimmt. Wenn du NULL als Wert für eine Spalte angibst, kannst du die Spalte trotz eines beliebigen Standardwerts auf NULL setzen (es sei denn, eine Einschränkung wurde speziell angewandt, um NULLs zu verhindern).
4.4 Kopieren von Zeilen aus einer Tabelle in eine andere
Problem
Du willst Zeilen aus einer Tabelle in eine andere kopieren, indem du eine Abfrage verwendest. Die Abfrage kann komplex oder einfach sein, aber letztendlich soll das Ergebnis in eine andere Tabelle eingefügt werden. Du möchtest zum Beispiel Zeilen aus der Tabelle DEPT in die Tabelle DEPT_EAST kopieren. Die Tabelle DEPT_EAST wurde bereits mit derselben Struktur (dieselben Spalten und Datentypen) wie DEPT erstellt und ist derzeit leer.
Lösung
Verwende die INSERT-Anweisung gefolgt von einer Abfrage, um die gewünschten Zeilen zu erzeugen:
1 insert into dept_east (deptno,dname,loc) 2 select deptno,dname,loc 3 from dept 4 where loc in ( 'NEW YORK','BOSTON' )
Diskussion
Folge der INSERT-Anweisung einfach mit einer Abfrage, die die gewünschten Zeilen zurückgibt. Wenn du alle Zeilen aus der Quelltabelle kopieren willst, schließe die WHERE-Klausel in der Abfrage aus. Wie bei einer normalen Einfügung musst du nicht explizit angeben, in welche Spalten du einfügst. Wenn du die Zielspalten jedoch nicht angibst, musst du die Daten in alle Spalten der Tabelle einfügen und auf die Reihenfolge der Werte in der SELECT-Liste achten, wie in Rezept 4.1 beschrieben.
4.5 Kopieren einer Tabellendefinition
Lösung
Oracle, MySQL und PostgreSQL
Verwende den Befehl CREATE TABLE mit einer Subquery, die keine Zeilen zurückgibt:
1 create table dept_2 2 as 3 select * 4 from dept 5 where 1 = 0
SQL Server
Verwende die INTO-Klausel mit einer Unterabfrage, die keine Zeilen zurückgibt:
1 select * 2 into dept_2 3 from dept 4 where 1 = 0
Diskussion
Oracle, MySQL und PostgreSQL
Bei der Verwendung von Create Table As Select (CTAS) werden alle Zeilen aus deiner Abfrage verwendet, um die neue Tabelle zu füllen, die du erstellst, es sei denn, du gibst eine falsche Bedingung in der WHERE-Klausel an. In der angegebenen Lösung bewirkt der Ausdruck "1 = 0" in der WHERE-Klausel der Abfrage, dass keine Zeilen zurückgegeben werden. Das Ergebnis der CTAS-Anweisung ist also eine leere Tabelle, die auf den Spalten in der SELECT-Klausel der Abfrage basiert.
SQL Server
Wenn du INTO zum Kopieren einer Tabelle verwendest, werden alle Zeilen aus deiner Abfrage verwendet, um die neue Tabelle zu füllen, die du erstellst, es sei denn, du gibst in der WHERE-Klausel deiner Abfrage eine falsche Bedingung an. In der angegebenen Lösung bewirkt der Ausdruck "1 = 0" im Prädikat der Abfrage, dass keine Zeilen zurückgegeben werden. Das Ergebnis ist eine leere Tabelle, die auf den Spalten in der SELECT-Klausel der Abfrage basiert.
4.6 Einfügen in mehrere Tabellen auf einmal
Problem
Du möchtest Zeilen, die von einer Abfrage zurückgegeben werden, in mehrere Zieltabellen einfügen. Zum Beispiel möchtest du Zeilen aus DEPT in die Tabellen DEPT_EAST, DEPT_WEST und DEPT_MID einfügen. Alle drei Tabellen haben die gleiche Struktur (gleiche Spalten und Datentypen) wie DEPT und sind derzeit leer.
Lösung
Die Lösung ist, das Ergebnis einer Abfrage in die Zieltabellen einzufügen. Der Unterschied zu Rezept 4.4 ist, dass du bei diesem Problem mehrere Zieltabellen hast.
Oracle
Verwende entweder die Anweisung INSERT ALL oder INSERT FIRST. Beide haben die gleiche Syntax, außer dass du zwischen den Schlüsselwörtern ALL und FIRST wählen musst. In der folgenden Anweisung wird INSERT ALL verwendet, damit alle möglichen Zieltabellen berücksichtigt werden:
1 insert all
2 when loc in ('NEW YORK','BOSTON') then
3 into dept_east (deptno,dname,loc) values (deptno,dname,loc)
4 when loc = 'CHICAGO' then
5 into dept_mid (deptno,dname,loc) values (deptno,dname,loc)
6 else
7 into dept_west (deptno,dname,loc) values (deptno,dname,loc)
8 select deptno,dname,loc
9 from dept
DB2
Füge in eine Inline-Ansicht ein, die eine UNION ALL für die einzufügenden Tabellen durchführt. Du musst auch sicherstellen, dass du die Tabellen mit Einschränkungen versiehst, die gewährleisten, dass jede Zeile in die richtige Tabelle eingefügt wird:
create table dept_east
( deptno integer,
dname varchar(10),
loc varchar(10) check (loc in ('NEW YORK','BOSTON')))
create table dept_mid
( deptno integer,
dname varchar(10),
loc varchar(10) check (loc = 'CHICAGO'))
create table dept_west
( deptno integer,
dname varchar(10),
loc varchar(10) check (loc = 'DALLAS'))
1 insert into (
2 select * from dept_west union all
3 select * from dept_east union all
4 select * from dept_mid
5 ) select * from dept
MySQL, PostgreSQL und SQL Server
Zum Zeitpunkt der Erstellung dieses Artikels unterstützen diese Anbieter keine mehrseitigen Einsätze.
Diskussion
Oracle
Das multitable Insert von Oracle verwendet WHEN-THEN-ELSE-Klauseln, um die Zeilen aus dem verschachtelten SELECT auszuwerten und sie entsprechend einzufügen. Im Beispiel dieses Rezepts würden INSERT ALL und INSERT FIRST das gleiche Ergebnis liefern, aber es gibt einen Unterschied zwischen den beiden. INSERT FIRST bricht die WHEN-THEN-ELSE-Auswertung ab, sobald es auf eine Bedingung stößt, die als wahr bewertet wird; INSERT ALL wertet alle Bedingungen aus, auch wenn die vorherigen Tests als wahr bewertet werden. So kannst du mit INSERT ALL dieselbe Zeile in mehr als eine Tabelle einfügen.
DB2
Meine DB2-Lösung ist ein ziemlicher Hack. Sie setzt voraus, dass für die Tabellen, in die eingefügt werden soll, Constraints definiert sind, um sicherzustellen, dass jede Zeile, die von der Subquery ausgewertet wird, in der richtigen Tabelle landet. Die Technik besteht darin, in eine Ansicht einzufügen, die als UNION ALL der Tabellen definiert ist. Wenn die Prüfbeschränkungen für die Tabellen in der INSERT-Anweisung nicht eindeutig sind (d. h. mehrere Tabellen haben dieselbe Prüfbeschränkung), weiß die INSERT-Anweisung nicht, wo sie die Zeilen einfügen soll, und schlägt fehl.
4.7 Blockieren von Einfügungen in bestimmte Spalten
Lösung
Erstelle eine Ansicht auf die Tabelle, die nur die Spalten zeigt, die du anzeigen willst. Erzwinge dann, dass alle Einfügungen über diese Ansicht laufen.
Um zum Beispiel eine Ansicht zu erstellen, die die drei Spalten in EMP zeigt:
create view new_emps as select empno, ename, job from emp
Gewähre denjenigen Benutzern und Programmen Zugriff auf diese Ansicht, die nur die drei Felder in der Ansicht ausfüllen dürfen. Gewähre diesen Benutzern keinen Einfügezugriff auf die Tabelle EMP. Die Benutzer können dann neue EMP-Datensätze erstellen, indem sie in die Ansicht NEW_EMPS einfügen, aber sie können keine Werte für andere Spalten als die drei in der Ansichtsdefinition angegebenen eingeben.
Diskussion
Wenn du in eine einfache Ansicht wie die in der Lösung einfügst, übersetzt dein Datenbankserver diese Einfügung in die zugrunde liegende Tabelle. Zum Beispiel die folgende Einfügung:
insert into new_emps (empno ename, job) values (1, 'Jonathan', 'Editor')
wird hinter den Kulissen in übersetzt:
insert into emp (empno ename, job) values (1, 'Jonathan', 'Editor')
Es ist auch möglich, aber vielleicht weniger nützlich, in eine Inline-Ansicht einzufügen (derzeit nur von Oracle unterstützt):
insert into
(select empno, ename, job
from emp)
values (1, 'Jonathan', 'Editor')
Das Einfügen von Ansichten ist ein komplexes Thema. Die Regeln werden sehr schnell kompliziert, wenn es sich nicht um die einfachsten Ansichten handelt. Wenn du vorhast, die Möglichkeit des Einfügens in Ansichten zu nutzen, musst du unbedingt die Dokumentation deines Anbieters zu diesem Thema konsultieren und vollständig verstehen.
4.8 Ändern von Datensätzen in einer Tabelle
Problem
Du möchtest die Werte für einige oder alle Zeilen in einer Tabelle ändern. Du möchtest zum Beispiel die Gehälter aller Mitarbeiter in Abteilung 20 um 10 % erhöhen. Die folgende Ergebnismenge zeigt die DEPTNO, ENAME und SAL für die Mitarbeiter in dieser Abteilung:
select deptno,ename,salfrom empwhere deptno = 20order by 1,3DEPTNO ENAME SAL ------ ---------- ---------- 20 SMITH 800 20 ADAMS 1100 20 JONES 2975 20 SCOTT 3000 20 FORD 3000
Du willst alle SAL-Werte um 10% erhöhen.
Diskussion
Verwende die UPDATE-Anweisung zusammen mit einer WHERE-Klausel, um festzulegen, welche Zeilen aktualisiert werden sollen; wenn du eine WHERE-Klausel ausschließt, werden alle Zeilen aktualisiert. Der Ausdruck SAL*1.10 in dieser Lösung gibt das um 10% erhöhte Gehalt zurück.
Wenn du dich auf eine Massenaktualisierung vorbereitest, möchtest du vielleicht eine Vorschau der Ergebnisse sehen. Das kannst du tun, indem du eine SELECT-Anweisung mit den Ausdrücken abgibst, die du in deine SET-Klauseln einfügen willst. Der folgende SELECT zeigt das Ergebnis einer 10%igen Gehaltserhöhung:
select deptno,ename,sal as orig_sal,sal*.10 as amt_to_add,sal*1.10 as new_salfrom empwhere deptno=20order by 1,5DEPTNO ENAME ORIG_SAL AMT_TO_ADD NEW_SAL ------ ------ -------- ---------- ------- 20 SMITH 800 80 880 20 ADAMS 1100 110 1210 20 JONES 2975 298 3273 20 SCOTT 3000 300 3300 20 FORD 3000 300 3300
Die Gehaltserhöhung wird in zwei Spalten aufgeteilt: eine für die Erhöhung gegenüber dem alten Gehalt und eine für das neue Gehalt.
4.9 Aktualisieren, wenn entsprechende Zeilen vorhanden sind
Problem
Du möchtest Zeilen in einer Tabelle aktualisieren, wenn entsprechende Zeilen in einer anderen Tabelle existieren. Wenn zum Beispiel ein Mitarbeiter in der Tabelle EMP_BONUS steht, möchtest du sein Gehalt (in der Tabelle EMP) um 20% erhöhen. Die folgende Ergebnismenge zeigt die aktuellen Daten in der Tabelle EMP_BONUS:
select empno, enamefrom emp_bonusEMPNO ENAME ---------- --------- 7369 SMITH 7900 JAMES 7934 MILLER
Lösung
Verwende eine Unterabfrage in der WHERE-Klausel deiner UPDATE-Anweisung, um Mitarbeiter in der Tabelle EMP zu finden, die auch in der Tabelle EMP_BONUS enthalten sind. Deine UPDATE-Anweisung wirkt dann nur auf diese Zeilen und ermöglicht es dir, ihr Gehalt um 20 % zu erhöhen:
1 update emp 2 set sal=sal*1.20 3 where empno in ( select empno from emp_bonus )
Diskussion
Die Ergebnisse der Subquery stellen die Zeilen dar, die in der Tabelle EMP aktualisiert werden. Das IN-Prädikat prüft, ob die EMPNO-Werte aus der Tabelle EMP in der Liste der EMPNO-Werte enthalten sind, die von der Subquery zurückgegeben werden. Wenn ja, werden die entsprechenden SAL-Werte aktualisiert.
Alternativ kannst du auch EXISTS statt IN verwenden:
update emp
set sal = sal*1.20
where exists ( select null
from emp_bonus
where emp.empno=emp_bonus.empno )
Du bist vielleicht überrascht, dass in der SELECT-Liste der EXISTS-Subquery NULL steht. Keine Angst, diese NULL hat keine negativen Auswirkungen auf die Aktualisierung. Es erhöht sogar die Lesbarkeit, da es die Tatsache unterstreicht, dass im Gegensatz zur Lösung mit einer Subquery mit IN-Operator die Aktualisierung (d.h. welche Zeilen aktualisiert werden) durch die WHERE-Klausel der Subquery und nicht durch die Werte gesteuert wird, die als Ergebnis der SELECT-Liste der Subquery zurückgegeben werden.
4.10 Aktualisieren mit Werten aus einer anderen Tabelle
Problem
Du möchtest Zeilen in einer Tabelle mit Werten aus einer anderen Tabelle aktualisieren. Du hast zum Beispiel eine Tabelle namens NEW_SAL, in der die neuen Gehälter für bestimmte Mitarbeiter gespeichert sind. Der Inhalt der Tabelle NEW_SAL sieht folgendermaßen aus:
select *from new_salDEPTNO SAL ------ ---------- 10 4000
Die Spalte DEPTNO ist der Primärschlüssel der Tabelle NEW_SAL. Du möchtest die Gehälter und Provisionen bestimmter Mitarbeiter in der Tabelle EMP anhand der Werte der Tabelle NEW_SAL aktualisieren, wenn es eine Übereinstimmung zwischen EMP.DEPTNO und NEW_SAL.DEPTNO gibt, EMP.SAL auf NEW_SAL.SAL aktualisieren und EMP.COMM auf 50% von NEW_SAL.SAL aktualisieren. Die Zeilen in EMP lauten wie folgt:
select deptno,ename,sal,commfrom emporder by 1DEPTNO ENAME SAL COMM ------ ---------- ---------- ---------- 10 CLARK 2450 10 KING 5000 10 MILLER 1300 20 SMITH 800 20 ADAMS 1100 20 FORD 3000 20 SCOTT 3000 20 JONES 2975 30 ALLEN 1600 300 30 BLAKE 2850 30 MARTIN 1250 1400 30 JAMES 950 30 TURNER 1500 0 30 WARD 1250 500
Lösung
Verwende einen Join zwischen NEW_SAL und EMP, um die neuen COMM-Werte zu finden und an die UPDATE-Anweisung zurückzugeben. In der Regel werden Aktualisierungen wie diese über eine korrelierte Subquery oder alternativ über eine CTE durchgeführt. Eine andere Technik besteht darin, eine View zu erstellen (traditionell oder inline, je nachdem, was deine Datenbank unterstützt) und diese View dann zu aktualisieren.
DB2
Verwende eine korrelierte Unterabfrage, um neue SAL- und COMM-Werte in EMP zu setzen. Verwende außerdem eine korrelierte Unterabfrage, um zu ermitteln, welche Zeilen in EMP aktualisiert werden sollen:
1 update emp e set (e.sal,e.comm) = (select ns.sal, ns.sal/2 2 from new_sal ns 3 where ns.deptno=e.deptno) 4 where exists ( select * 5 from new_sal ns 6 where ns.deptno = e.deptno )
MySQL
Nimm sowohl EMP als auch NEW_SAL in die UPDATE-Klausel der UPDATE-Anweisung auf und verbinde sie in der WHERE-Klausel:
1 update emp e, new_sal ns 2 set e.sal=ns.sal, 3 e.comm=ns.sal/2 4 where e.deptno=ns.deptno
Oracle
Die Methode für die DB2-Lösung wird auch für Oracle funktionieren, aber alternativ kannst du auch eine Inline-Ansicht aktualisieren:
1 update ( 2 select e.sal as emp_sal, e.comm as emp_comm, 3 ns.sal as ns_sal, ns.sal/2 as ns_comm 4 from emp e, new_sal ns 5 where e.deptno = ns.deptno 6 ) set emp_sal = ns_sal, emp_comm = ns_comm
PostgreSQL
Die Methode, die für die DB2-Lösung verwendet wird, funktioniert auch für PostgreSQL, aber du könntest auch (ganz bequem) direkt in der UPDATE-Anweisung joinen:
1 update emp 2 set sal = ns.sal, 3 comm = ns.sal/2 4 from new_sal ns 5 where ns.deptno = emp.deptno
SQL Server
Die Methode, die für die DB2-Lösung verwendet wird, funktioniert auch für SQL Server, aber alternativ kannst du (ähnlich wie bei der PostgreSQL-Lösung) direkt in derUPDATE-Anweisung joinen:
1 update e 2 set e.sal = ns.sal, 3 e.comm = ns.sal/2 4 from emp e, 5 new_sal ns 6 where ns.deptno = e.deptno
Diskussion
Bevor wir auf die verschiedenen Lösungen eingehen, sollten wir etwas Wichtiges zu Aktualisierungen erwähnen, die Abfragen verwenden, um neue Werte zu liefern. Eine WHERE-Klausel in der Unterabfrage einer korrelierten Aktualisierung ist nicht dasselbe wie die WHERE-Klausel der Tabelle, die aktualisiert wird. Wenn du dir die UPDATE-Anweisung im Abschnitt "Problem" ansiehst, wird der Join auf DEPTNO zwischen EMP und NEW_SAL durchgeführt und gibt Zeilen an die SET-Klausel der UPDATE-Anweisung zurück. Für die Mitarbeiter in DEPTNO 10 werden gültige Werte zurückgegeben, weil es eine passende DEPTNO in der Tabelle NEW_SAL gibt. Aber was ist mit den Angestellten in den anderen Abteilungen? In NEW_SAL gibt es keine anderen Abteilungen, also werden SAL und COMM für die Beschäftigten in den DEPTNOs 20 und 30 auf NULL gesetzt. Die einzige Möglichkeit, die Anzahl der Zeilen einer Tabelle in SQL einzuschränken, ist die Verwendung einer WHERE-Klausel, es sei denn, du verwendest LIMIT oder TOP oder einen anderen Mechanismus, den dein Anbieter zur Begrenzung der Anzahl der Zeilen in einer Ergebnismenge anbietet. Um dieses UPDATE korrekt durchzuführen, musst du eine WHERE-Klausel für die zu aktualisierende Tabelle und eine WHERE-Klausel in der korrelierten Subquery verwenden.
DB2
Um sicherzustellen, dass du nicht jede Zeile in der Tabelle EMP aktualisierst, erinnere dich daran, eine korrelierte Subquery in die WHERE-Klausel des UPDATE aufzunehmen. Es reicht nicht aus, die Verknüpfung (die korrelierte Unterabfrage) in der SET-Klausel auszuführen. Durch die Verwendung einer WHERE-Klausel in der UPDATE-Klausel stellst du sicher, dass nur die Zeilen in EMP aktualisiert werden, die mit DEPTNO in der Tabelle NEW_SAL übereinstimmen. Das gilt für alle RDBMS.
Oracle
In der Oracle-Lösung mit der Update-Join-Ansicht verwendest du Equi-Joins, um zu bestimmen, welche Zeilen aktualisiert werden. Du kannst überprüfen, welche Zeilen aktualisiert werden, indem du die Abfrage unabhängig davon ausführst. Um diese Art von UPDATE erfolgreich nutzen zu können, musst du zunächst das Konzept der Key Preservation verstehen. Die Spalte DEPTNO der Tabelle NEW_SAL ist der Primärschlüssel dieser Tabelle; ihre Werte sind also innerhalb der Tabelle eindeutig. Bei der Verknüpfung von EMP und NEW_SAL ist NEW_SAL.DEPTNO in der Ergebnismenge jedoch nicht eindeutig, wie hier gezeigt:
select e.empno, e.deptno e_dept, ns.sal, ns.deptno ns_deptnofrom emp e, new_sal nswhere e.deptno = ns.deptnoEMPNO E_DEPT SAL NS_DEPTNO ----- ---------- ---------- ---------- 7782 10 4000 10 7839 10 4000 10 7934 10 4000 10
Damit Oracle diese Verknüpfung aktualisieren kann, muss eine der Tabellen schlüsselgesichert sein, d. h., wenn ihre Werte in der Ergebnismenge nicht eindeutig sind, sollten sie zumindest in der Tabelle, aus der sie stammen, eindeutig sein. In diesem Fall hat NEW_SAL einen Primärschlüssel auf DEPTNO, wodurch er in der Tabelle eindeutig ist. Da er in seiner Tabelle eindeutig ist, kann er mehrfach in der Ergebnismenge auftauchen und wird trotzdem als schlüsselgesichert angesehen, so dass die Aktualisierung erfolgreich abgeschlossen werden kann.
PostgreSQL, SQL Server und MySQL
Die Syntax unterscheidet sich ein wenig zwischen diesen Plattformen, aber die Technik ist dieselbe. Die Möglichkeit, direkt in der UPDATE-Anweisung zu verknüpfen, ist äußerst praktisch. Da du die zu aktualisierende Tabelle angibst (die Tabelle, die hinter dem UPDATE-Schlüsselwort steht), gibt es keine Verwirrung darüber, welche Tabellenzeilen geändert werden. Da du außerdem Joins in der Aktualisierung verwendest (da es eine explizite WHERE-Klausel gibt), kannst du einige der Fallstricke bei der Codierung von korrelierten Subquery-Aktualisierungen vermeiden; insbesondere wenn du hier einen Join auslassen würdest, wäre es offensichtlich, dass du ein Problem hast.
4.11 Zusammenführen von Datensätzen
Problem
Du möchtest Datensätze in einer Tabelle bedingt einfügen, aktualisieren oder löschen, je nachdem, ob entsprechende Datensätze existieren. (Wenn ein Datensatz vorhanden ist, aktualisiere ihn; wenn nicht, füge ihn ein; wenn eine Zeile nach der Aktualisierung eine bestimmte Bedingung nicht erfüllt, lösche sie). Du möchtest zum Beispiel die Tabelle EMP_COMMISSION so ändern, dass:
-
Wenn ein Angestellter in EMP_COMMISSION auch in der Tabelle EMP existiert, dann aktualisiere seine Provision (COMM) auf 1000.
-
Wenn die SAL aller Arbeitnehmer, deren COMM auf 1000 aktualisiert wird, kleiner als 2000 ist, lösche sie (sie sollten nicht in EMP_[.keep-together] COMMISSION vorhanden sein).
-
Ansonsten fügst du die Werte EMPNO, ENAME und DEPTNO aus der Tabelle EMP in die Tabelle EMP_COMMISSION ein.
Im Wesentlichen willst du entweder ein UPDATE oder ein INSERT ausführen, je nachdem, ob eine bestimmte Zeile von EMP eine Übereinstimmung in EMP_COMMISSION hat. Dann willst du ein DELETE ausführen, wenn das Ergebnis eines UPDATE eine zu hohe Provision verursacht.
Die folgenden Zeilen befinden sich derzeit in den Tabellen EMP und EMP_COMMISSION :
select deptno,empno,ename,commfrom emporder by 1DEPTNO EMPNO ENAME COMM ------ ---------- ------ ---------- 10 7782 CLARK 10 7839 KING 10 7934 MILLER 20 7369 SMITH 20 7876 ADAMS 20 7902 FORD 20 7788 SCOTT 20 7566 JONES 30 7499 ALLEN 300 30 7698 BLAKE 30 7654 MARTIN 1400 30 7900 JAMES 30 7844 TURNER 0 30 7521 WARD 500select deptno,empno,ename,commfrom emp_commissionorder by 1DEPTNO EMPNO ENAME COMM ---------- ---------- ---------- ---------- 10 7782 CLARK 10 7839 KING 10 7934 MILLER
Lösung
Die Anweisung, die dieses Problem lösen soll, ist die MERGE-Anweisung, die je nach Bedarf entweder ein UPDATE oder ein INSERT durchführen kann. Ein Beispiel:
1 merge into emp_commission ec 2 using (select * from emp) emp 3 on (ec.empno=emp.empno) 4 when matched then 5 update set ec.comm = 1000 6 delete where (sal < 2000) 7 when not matched then 8 insert (ec.empno,ec.ename,ec.deptno,ec.comm) 9 values (emp.empno,emp.ename,emp.deptno,emp.comm)
MySQL verfügt derzeit nicht über eine MERGE-Anweisung; ansonsten sollte diese Abfrage auf jedem RDBMS in diesem Buch und in vielen anderen funktionieren.
Diskussion
Der Join in Zeile 3 der Lösung bestimmt, welche Zeilen bereits existieren und aktualisiert werden. Der Join erfolgt zwischen EMP_COMMISSION (alias EC) und der Unterabfrage (alias EMP). Wenn der Join erfolgreich ist, gelten die beiden Zeilen als "übereinstimmend" und das in der WHEN MATCHED-Klausel angegebene UPDATE wird ausgeführt. Andernfalls wird keine Übereinstimmung gefunden und das INSERT in WHEN NOT MATCHED wird ausgeführt. So werden Zeilen aus der Tabelle EMP, die keine entsprechenden Zeilen auf der Basis von EMPNO in der Tabelle EMP_COMMISSION haben, in EMP_COMMISSION eingefügt. Von allen Angestellten in der Tabelle EMP sollten nur die in DEPTNO 10 ihre COMM in EMP_COMMISSION aktualisieren, während die restlichen Angestellten eingefügt werden. Da MILLER in DEPTNO 10 ist, ist er ein Kandidat für eine Aktualisierung seiner COMM, aber da seine SAL unter 2.000 liegt, wird sie aus EMP_COMMISSION gelöscht.
4.12 Alle Datensätze aus einer Tabelle löschen
Diskussion
Wenn du den Befehl DELETE ohne WHERE-Klausel verwendest, löschst du alle Zeilen aus der angegebenen Tabelle. Manchmal ist TRUNCATE, das sich auf Tabellen bezieht und daher die WHERE-Klausel nicht verwendet, vorzuziehen, da es schneller ist. Zumindest in Oracle kann TRUNCATE jedoch nicht rückgängig gemacht werden. Du solltest die Dokumentation des Anbieters sorgfältig prüfen, um einen detaillierten Überblick über die Leistungs- und Rückgängigmachungsunterschiede zwischen TRUNCATE und DELETE in deinem spezifischen RDBMS zu erhalten.
4.13 Löschen von bestimmten Datensätzen
Lösung
Verwende den Befehl DELETE mit einer WHERE-Klausel, die angibt, welche Zeilen gelöscht werden sollen. Um zum Beispiel alle Mitarbeiter in Abteilung 10 zu löschen, verwende Folgendes:
delete from emp where deptno = 10
Diskussion
Wenn du eine WHERE-Klausel mit dem DELETE-Befehl verwendest, kannst du eine Teilmenge von Zeilen in einer Tabelle löschen, anstatt alle Zeilen. Vergiss nicht zu überprüfen, ob du die richtigen Daten löschst, indem du dir die Auswirkungen deiner WHERE-Klausel mit SELECT ansiehst - selbst in einer einfachen Situation können falsche Daten gelöscht werden. Im vorherigen Beispiel könnte ein Tippfehler dazu führen, dass die Mitarbeiter der Abteilung 20 statt der Abteilung 10 gelöscht werden!
4.14 Löschen eines einzelnen Datensatzes
Lösung
Dies ist ein Spezialfall von Rezept 4.13. Wichtig ist, dass dein Auswahlkriterium so eng gefasst ist, dass es nur den einen Datensatz enthält, den du löschen willst. Oft willst du anhand des Primärschlüssels löschen. Beispiel: Du willst den Mitarbeiter CLARK (EMPNO 7782) löschen:
delete from emp where empno = 7782
Diskussion
Beim Löschen geht es immer darum, die zu löschenden Zeilen zu identifizieren, und die Auswirkung eines DELETEs hängt immer von der WHERE-Klausel ab. Wenn du die WHERE-Klausel weglässt, erstreckt sich ein DELETE auf die gesamte Tabelle. Indem du Bedingungen in die WHERE-Klausel schreibst, kannst du den Bereich auf eine Gruppe von Datensätzen oder auf einen einzelnen Datensatz eingrenzen. Wenn du einen einzelnen Datensatz löschst, solltest du ihn normalerweise anhand seines Primärschlüssels oder eines seiner eindeutigen Schlüssel identifizieren.
Warnung
Wenn dein Löschkriterium auf einem Primärschlüssel oder einem eindeutigen Schlüssel basiert, kannst du sicher sein, dass nur ein Datensatz gelöscht wird. (Das liegt daran, dass dein RDBMS nicht zulässt, dass zwei Zeilen die gleichen Primär- oder Unique-Key-Werte enthalten.) Andernfalls solltest du zuerst prüfen, ob du nicht versehentlich mehr Datensätze löschst, als du eigentlich vorhast.
4.15 Löschen von Verletzungen der referentiellen Integrität
Lösung
Verwende das NOT EXISTS-Prädikat mit einer Subquery, um die Gültigkeit von Abteilungsnummern zu prüfen:
delete from emp
where not exists (
select * from dept
where dept.deptno = emp.deptno
)
Alternativ kannst du die Abfrage auch mit einem NOT IN-Prädikat schreiben:
delete from emp where deptno not in (select deptno from dept)
Diskussion
Beim Löschen geht es eigentlich nur um das Auswählen: Die eigentliche Arbeit besteht darin, die Bedingungen für die WHERE-Klausel zu schreiben, um die Datensätze, die du löschen willst, richtig zu beschreiben.
Die NOT EXISTS-Lösung verwendet eine korrelierte Unterabfrage, um zu prüfen, ob es einen Datensatz in DEPT mit einer DEPTNO gibt, die mit der eines bestimmten EMP-Datensatzes übereinstimmt. Wenn ein solcher Datensatz existiert, wird der EMP-Datensatz beibehalten. Andernfalls wird er gelöscht. Jeder EMP-Datensatz wird auf diese Weise geprüft.
Die IN-Lösung verwendet eine Unterabfrage, um eine Liste der gültigen Abteilungsnummern abzurufen. Die DEPTNOs der einzelnen EMP-Datensätze werden dann mit dieser Liste abgeglichen. Wenn ein EMP-Datensatz mit einer DEPTNO gefunden wird, die nicht in der Liste enthalten ist, wird der EMP-Datensatz gelöscht.
4.16 Löschen von doppelten Datensätzen
Problem
Du möchtest doppelte Datensätze aus einer Tabelle löschen. Betrachte die folgende Tabelle:
create table dupes (id integer, name varchar(10))insert into dupes values (1, 'NAPOLEON')insert into dupes values (2, 'DYNAMITE')insert into dupes values (3, 'DYNAMITE')insert into dupes values (4, 'SHE SELLS')insert into dupes values (5, 'SEA SHELLS')insert into dupes values (6, 'SEA SHELLS')insert into dupes values (7, 'SEA SHELLS')select * from dupes order by 1ID NAME ---------- ---------- 1 NAPOLEON 2 DYNAMITE 3 DYNAMITE 4 SHE SELLS 5 SEA SHELLS 6 SEA SHELLS 7 SEA SHELLS
Für jede Gruppe doppelter Namen, wie z.B. SEA SHELLS, willst du eine ID beibehalten und den Rest löschen. Im Fall von SEA SHELLS ist es dir egal, ob du die Zeilen 5 und 6 oder die Zeilen 5 und 7 oder die Zeilen 6 und 7 löschst, aber am Ende willst du nur einen Datensatz für SEA SHELLS.
Lösung
Verwende eine Unterabfrage mit einer Aggregatfunktion wie MIN, um willkürlich die ID auszuwählen, die beibehalten werden soll (in diesem Fall wird nur der NAME mit dem kleinsten Wert für ID nicht gelöscht):
1 delete from dupes 2 where id not in ( select min(id) 3 from dupes 4 group by name )
Für MySQL-Benutzer brauchst du eine etwas andere Syntax, da du beim Löschen nicht zweimal auf dieselbe Tabelle verweisen kannst (zum Zeitpunkt dieses Artikels):
1 delete from dupes 2 where id not in 3 (select min(id) 4 from (select id,name from dupes) tmp 5 group by name)
Diskussion
Wenn du Duplikate löschen willst, musst du zunächst genau definieren, was es bedeutet, wenn zwei Zeilen als "Duplikate" voneinander gelten. In meinem Beispiel in diesem Rezept bedeutet "doppelt", dass zwei Datensätze denselben Wert in der Spalte NAME enthalten. Nach dieser Definition kannst du anhand einer anderen Spalte zwischen den einzelnen Duplikaten unterscheiden, welche Datensätze beibehalten werden sollen. Am besten ist es, wenn diese Unterscheidungsspalte (oder Spalten) ein Primärschlüssel ist. Wir haben die ID-Spalte verwendet, die eine gute Wahl ist, weil keine zwei Datensätze die gleiche ID haben.
Der Schlüssel zur Lösung ist, dass du nach den doppelten Werten gruppierst (in diesem Fall nach NAME) und dann eine Aggregatfunktion verwendest, um nur einen Schlüsselwert zu behalten. Die Unterabfrage im Beispiel "Lösung" gibt die kleinste ID für jeden NAMEN zurück, also die Zeile, die du nicht löschen willst:
select min(id)from dupesgroup by nameMIN(ID) ----------- 2 1 5 4
Das DELETE löscht dann alle IDs in der Tabelle, die nicht von der Subquery zurückgegeben werden (in diesem Fall die IDs 3, 6 und 7). Wenn du nicht weißt, wie das funktioniert, führe zuerst die Unterabfrage aus und gib den NAMEN in der SELECT-Liste an:
select name, min(id)from dupesgroup by nameNAME MIN(ID) ---------- ---------- DYNAMITE 2 NAPOLEON 1 SEA SHELLS 5 SHE SELLS 4
Die von der Subquery zurückgegebenen Zeilen sind diejenigen, die beibehalten werden sollen. Das NOT IN-Prädikat in der DELETE-Anweisung bewirkt, dass alle anderen Zeilen gelöscht werden.
4.17 Löschen von Datensätzen, die von einer anderen Tabelle referenziert werden
Problem
Du möchtest Datensätze aus einer Tabelle löschen, wenn diese Datensätze in einer anderen Tabelle referenziert werden. Die folgende Tabelle mit dem Namen DEPT_ACCIDENTS enthält eine Zeile für jeden Unfall, der sich in einem Produktionsbetrieb ereignet. Jede Zeile enthält die Abteilung, in der sich ein Unfall ereignet hat, und die Art des Unfalls.
create table dept_accidents( deptno integer,accident_name varchar(20) )insert into dept_accidents values (10,'BROKEN FOOT')insert into dept_accidents values (10,'FLESH WOUND')insert into dept_accidents values (20,'FIRE')insert into dept_accidents values (20,'FIRE')insert into dept_accidents values (20,'FLOOD')insert into dept_accidents values (30,'BRUISED GLUTE')select * from dept_accidentsDEPTNO ACCIDENT_NAME ---------- -------------------- 10 BROKEN FOOT 10 FLESH WOUND 20 FIRE 20 FIRE 20 FLOOD 30 BRUISED GLUTE
Du möchtest aus der EMP die Datensätze der Mitarbeiter löschen, die in einer Abteilung arbeiten, in der es drei oder mehr Unfälle gab.
Lösung
Verwende eine Unterabfrage und die Aggregatfunktion ZÄHLEN, um die Abteilungen mit drei oder mehr Unfällen zu finden. Lösche dann alle Mitarbeiter, die in diesen Abteilungen arbeiten:
1 delete from emp 2 where deptno in ( select deptno 3 from dept_accidents 4 group by deptno 5 having count(*) >= 3 )
Diskussion
Die Unterabfrage ermittelt, welche Abteilungen drei oder mehr Unfälle haben:
select deptnofrom dept_accidentsgroup by deptnohaving count(*) >= 3DEPTNO ---------- 20
Mit DELETE werden dann alle Mitarbeiter in den Abteilungen gelöscht, die von der Unterabfrage zurückgegeben werden (in diesem Fall nur in Abteilung 20).
4.18 Resümee
Das Einfügen und Aktualisieren von Daten scheint weniger Zeit in Anspruch zu nehmen als das Abfragen von Daten, und im Rest des Buches werden wir uns auf Abfragen konzentrieren. Die Fähigkeit, die Daten in einer Datenbank zu pflegen, ist jedoch von grundlegender Bedeutung für ihren Zweck, und diese Rezepte sind ein wichtiger Teil der Fähigkeiten, die für die Pflege einer Datenbank erforderlich sind. Einige dieser Befehle, vor allem Befehle, die Daten entfernen oder löschen, können dauerhafte Folgen haben. Überprüfe alle Daten, die du löschen willst, in der Vorschau, um sicherzugehen, dass du wirklich löschst, was du vorhast, und mache dich damit vertraut, was in deinem speziellen RDBMS rückgängig gemacht werden kann und was nicht.
Get SQL Kochbuch, 2. Auflage now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
