update über mehrere Tabellen
delete über mehrere Tabellen

update über mehrere Tabellen

nach oben

Es sind sehr leicht Umstände denkbar, bei denen auch ein udpdate oder ein delete über einen join durchgeführt werden muss. Betrachten wir einmal folgende zwei Tabellen.

In dieser Situation sind alle möglichen Szenarien vorstellbar. Vorstellbar ist, dass der Kunde Hans Müller stirbt. In diesem Falle wäre es unter Umständen sinnvoll, ihn komplett zu löschen mitsamt den Bestellungen, die er jemals getätigt hat, weil wir für eine aktuelle Analyse an nicht vorhandenen Kunden nicht interessiert sind. Denkbar ist aber auch, dass wir ein neues Rabattsystem einführen und alle Kunden, die eine gewisse Umsatzgrenze überschreiten, in einen neuen Tarif einsortieren wollen. Bevor wir zeigen können, wie das funktionniert, sehen wir uns mit unserem alten Problem konfrontiert. Wir müssen die Tabellen erst generieren und dann einspulen. Das machen wir am besten in altbewährter Manier mit einem batch file, der so aussieht.

use testlauf;

create table Kundschaft (
Kundennummer char(5) NOT NULL,
Name char(30),
Vorname char(30),
Rabattstufe int(2),
PRIMARY KEY(Kundennummer));

create table Bestellungen(
Kundennummer char(10) NOT NULL,
Rechnungsnummer char(30) NOT NULL,
Umsatz int(5),
PRIMARY KEY(Rechnungsnummer),Index(Kundennummer));

INSERT INTO kundschaft
(Kundennummer,Name,Vorname,Rabattstufe) VALUES
('K-1', 'Müller', 'Hans',1),
('K-2', 'Maier', 'Ernst',2),
('K-3', 'Schulze', 'Fritz',1),
('K-4', 'Schmidt', 'Egon',2);

INSERT INTO Bestellungen
(Kundennummer,Rechnungsnummer,Umsatz) VALUES
('K-1', 'Berlin-034', '5000'),
('K-4', 'Hamburg-098', '3000'),
('K-2', 'Freiburg-09878', '2000'),
('K-1', 'Dresden-0494', '1500'),
('K-3', 'Berlin-0355', '6000'),
('K-4', 'Biberach-455', '2800'),
('K-1', 'Bielefeld-4354', '3100'),
('K-3', 'Lindau-45444', '2900'),
('K-2', 'Gelsenkirchen-4544', '3500');

select * from kundschaft;
select * from Bestellungen;

Wir lösen diesen batch Prozess mit dieser Zeile aus.

C:\>c:\mysql\bin\mysql -t <c:\mysql_handbuch\testlauf2.sql >c:\ergebnis3.txt

Und erhalten als Ergebnis, in der Datei ergebnis3.txt das.

	Kundennummer		Name		Vorname
	K-1		Müller		Hans
	K-2		Maier		Ernst
	K-3		Schulze		Fritz
	K-4		Schmidt		Egon

	Kundennummer		Rechnungsnummer		Umsatz
	K-1		Berlin-034		5000
	K-4		Hamburg-098		3000
	K-2		Freiburg-09878		2000
	K-1		Dresden-0494		1500
	K-3		Berlin-0355		6000
	K-4		Biberach-455		2800
	K-1		Bielefeld-4354		3100
	K-3		Lindau-45444		2900
	K-2		Gelsenkirchen-4544		3500

Alle Kunden in einen neuen Tarif einordnen, die mehr als 8000 Umsatz haben
Um uns einen Überblick zu verschaffen, zeigen wir erstmal die entsprechenden Kunden an.

mysql> select Kundschaft.Kundennummer,Name,sum(Umsatz) from
-> Kundschaft,Bestellungen where
-> Kundschaft.Kundennummer=Bestellungen.Kundennummer
-> group by(Kundennummer);

	Kundennummer		Name		sum(Umsatz)
	K-1		Müller		9600
	K-2		Maier		5500
	K-3		Schulze		8900
	K-4		Schmidt		5800

4 rows in set (0.22 sec)
mysql>

Da Kundennummer in diesem Beispiel in beiden Tabellen enthalten ist, hätten wir auch einen natural join machen können.

mysql> select Kundschaft.Kundennummer,Name,sum(Umsatz) from
-> Kundschaft natural join Bestellungen group by(Kundennummer);

	Kundennummer		Name		sum(Umsatz)
	K-1		Müller		9600
	K-2		Maier		5500
	K-3		Schulze		8900
	K-4		Schmidt		5800

4 rows in set (0.06 sec)
mysql>

Wie deutlich sichtbar, haben Müller und Schulze mehr als 8000 Umsatz. Beide gehören im Moment noch zur Rabattstufe 1. Dies soll nun geändert werden. Die, die mehr als 8000 Umsatz haben, sollen in die Rabattstufe 3 eingestuft werden. Leider kommt jetzt die grosse Entäuschung. mysql erlaubt joins nur in Verbindung mit select. Wir brauchen also ein work around. Ab Version mysql 4.1.0 soll sich dies ändern, dann soll ein join auch mit update möglich sein. Im Moment gibt es jedoch keine allgemeine Lösung für Probleme dieser Art, man muss sich also von Fall zu Fall überlegen, wie man es machen kann. In diesem Falle könnte man folgendes tun.

1) Eine Kopie von Kundschaft als temporary table generieren, der zusätzlich noch die Spalte Umsatz hat
2) Anhand dieser einen Tabelle das update durchführen
3) mit alter table die Spalte Umsatz löschen
4) die urprüngliche Tabelle Kundschaft löschen
5) die temporary table in Kundschaft umebennen

Das sieht dann so aus.

use testlauf;

create table tmp (
Kundennummer char(5) NOT NULL,
Name char(30),
Vorname char(30),
Rabattstufe int(2),
Umsatz int(5),
PRIMARY KEY(Kundennummer));

insert into tmp
(Kundennummer,Name,Vorname,Rabattstufe,Umsatz)
select
Kundschaft.Kundennummer,Name,Kundschaft.Vorname,Rabattstufe,sum(Umsatz)
from Kundschaft,Bestellungen where Kundschaft.Kundennummer=Bestellungen.Kundennummer
group by Bestellungen.Kundennummer;

update tmp set Rabattstufe=3 where Umsatz>=8000;
alter table tmp drop Umsatz;

drop table Kundschaft;
rename table tmp to Kundschaft;

select * from Kundschaft;

Wir rufen diesen batch Prozess, wie schon mehrmals erwähnt, mit dieser Zeile auf.

C:\>c:\mysql\bin\mysql -t <c:\mysql_handbuch\testat4.sql >c:\ergebnis4.txt

Und erhalten als Ergebnis, abgespeichert in der Datei ergebnis4.txt das was wir uns erhofft hatten.

	Kundennummer		Name		Vorname		Rabattstufe
	K-1		Müller		Hans		3
	K-2		Maier		Ernst		2
	K-3		Schulze		Fritz		3
	K-4		Schmidt		Egon		2

Wie deutlich zu erkennen, wurde die Rabattstufe bei Müller und Schulze auf 3 gesetzt.

delete über mehrere Tabellen

nach oben

Wenn ein Kunde Konkurs angemeldet hat, ist es keine gute Idee, ihm weiterhin Ware zu senden. Er braucht als Kunde auch nicht mehr geführt werden. Ein Problem, dass man natürlich mit zwei sql statements einfach lösen kann. (delete from Kundschaft where Kundennummer="K-XX", delete from Bestellungen where Kundennummer="K-XX"). Ab Version mysql 4.0.0 unterstützt mysql aber auch joins in delete statements, so dass das Problem auch mit einem einzigen SQL Statement gelöst werden kann. Es sei konzediert, dass man in einem so einfachen Fall, schneller zwei Statements geschrieben hat. Denkbar sind aber auch kompliziertere Fälle. Angenommen ein BWLer hätte die neuesten Erkenntnisse aus der Kostentheorie angwendet und festgestellt, dass ein kleiner Auftrag diesselben Transaktionkosten verursacht wie ein grosser und folglich beschlossen, Kunden unter einem bestimmten Umsatz nicht mehr zu bedienen. Dann müssten alle Kunden, die weniger als X Umsatz haben plus die entsprechenden Bestellungen gelöscht werden. Beginnen wir also mit dem einfachen Fall. Einen Kunden und alle dazugehörigen Bestellungen löschen, also dem Konkursfall. Wie bereits erwähnt funktionniert join mit delete erst ab Version 4.0.0 und höher. Wer das Beispiel nachvollziehen will, muss sich also diese Version von www.mysql.com runterladen und selbige dann mit next,yes,ok, weiter installieren. Die Tabellen, die dem Beispiel zugrunde liegen sind dann neu zu initialisieren. Der Skript dazu ist oben abgebildet. Will man also alle Kunden mit der Kundennummer K-2 und alle dazugehörigen Bestellungen löschen und das Problem mit einem SQL Statement lösen, kann man sowas machen.

use testlauf;

delete Kundschaft,Bestellungen from Kundschaft,Bestellungen where
Kundschaft.Kundennummer=Bestellungen.Kundennummer
and Kundschaft.Kundennummer="K-2";

select * from kundschaft;
select * from bestellungen;

Diesen batch Prozess, wir hätten das SQL Statement auch vom mysql Client heraus absetzen können, können wir mit dieser Zeile auslösen.

C:\>c:\mysql\bin\mysql -t ergebnis6.txt

In der Datei ergebnis6.txt sehen wir dann das Ergebnis.

	Kundennummer		Name		Vorname		Rabattstufe
	K-1		Müller		Hans		1
	K-3		Schulze		Fritz		1
	K-4		Schmidt		Egon		2

	Kundennummer		Rechnungsnummer		Umsatz
	K-1		Berlin-034		5000
	K-4		Hamburg-098		3000
	K-1		Dresden-0494		1500
	K-3		Berlin-0355		6000
	K-4		Biberach-455		2800
	K-1		Bielefeld-4354		3100
	K-3		Lindau-45444		2900

Wie deutlich sichtbar, wurde der Kunde mit der Kundennummer K-2 sowie alle dazugehörigen Bestellungen gelöscht. Die Syntax wundert. Die Erklärung aus der Dokumentation von mysql liest sich dann so.

The idea is that only matching rows from the tables listed
before the FROM or before the USING clause are
deleted. The effect is that you can delete rows
from many tables at the same time and also have
additional tables that are used for searching.

Das heisst, vor dem from stehen jetzt die Tabellen, aus denen gelöscht wird und nach from (genau genommen zwischen from und where) die Tabellen, die für die Suche verwendet werden. Widmen wir uns nun unserem zweiten Problem. Wir wollen alle Kunden eliminieren, die weniger als 9000 Umsatz haben. Also deren Bestellungen und den Kunden selber. Aus der Tabelle oben ist ersichtlich, wer dann gelöscht wird,nämlich Schulze und Schmidt, weil wir den Maier ja schon eliminiert haben. Leider ist aber ein statement dieser Art

use testlauf;

delete from Kundschaft,Bestellungen using Kundschaft,Bestellungen where
Kundschaft.Kundennummer=Bestellungen.Kundennummer
group by Bestellungen.Kundennummer
having sum(Bestellungen.Umsatz)<9000;

nicht oder noch nicht möglich, so dass wir auch dieses Problem nur über mehrere SQL Statements lösen können. Hierbei kann folgendermassen vorgegangen werden.

Das sieht dann so aus.
use testlauf;

create table testat (
Kundennummer char(5) NOT NULL,
Gesamtumsatz int(2),
index(Kundennummer));

insert into testat
(Gesamtumsatz,Kundennummer)
select sum(Bestellungen.Umsatz),Kundschaft.Kundennummer from Kundschaft,Bestellungen
where Kundschaft.Kundennummer=Bestellungen.Kundennummer
group by(Bestellungen.Kundennummer) having sum(Bestellungen.Umsatz)<=9000;

delete Kundschaft,Bestellungen
from Kundschaft,Bestellungen,testat
where testat.Kundennummer=Bestellungen.Kundennummer and testat.Kundennummer=Kundschaft.Kundennummer;

drop table testat;

select * from Bestellungen;
select * from Kundschaft;

Wir lösen das wieder so aus.

C:\>c:\mysql\bin\mysql -t <c:\mysql_handbuch\testat6.sql >c:\ergebnis8.txt

Das Ergebnis, das wir in ergebnis8.txt erhalten, sieht dann so aus.

	Kundennummer		Rechnungsnummer		Umsatz
	K-1		Berlin-034		5000
	K-1		Dresden-0494		1500
	K-1		Bielefeld-4354		3100

	Kundennummer		Name		Vorname		Rabattstufe
	K-1		Müller		Hans		1

Wir sehen also, dass nur noch der eine Kunde übriggeblieben ist, der mehr als 9000 Umsatz hat.

vorhergehendes Kapitel	nach oben	nächstes Kapitel