Sie sind nun wieder eingeladen, die Diskussion spezieller technischer Probleme mit zu verfolgen. Bitte schicken Sie Fragen, Anregungen oder Antworten zu den vorgestellten Themen – ebenso wie Ihre Kritik – an unsere e-Mail-Adressen: dieter.bender@midrangemagazin.de oder redaktion@midrangemagazin.de

Frage:

Kann man mit einer SQL-Anweisung doppelte Sätze in einer Datei löschen und dabei jeweils einen als gültigen stehen lassen?

Antwort:

Fast alle Fragen, die mit „Kann man mit SQL…“ anfangen, sind mit „Ja“ zu beantworten; die Fragen, die sich dann allerdings stellen, z. B. nach dem „Wie“ und nach der „Dauer“, erfordern dann schon mehr Überlegung zur Beantwortung. Ich möchte an folgendem Beispiel versuchen, die Arbeitsweise zur Lösung solcher Fragen zu veranschaulichen.

Doppelte Sätze – im engeren Sinne – kennt SQL nicht. Aus SQL-Sicht legt man eine Constraint für eine Datei an, wenn bestimmte Kombinationen von Werten nicht doppelt vorkommen sollen – und dann gibt es keine solche Sätze doppelt. Für ein kleines Beispiel benutze ich nun einen Kundenstamm – ein doppelter Satz soll nur dann vorliegen, wenn Vorname, Nachname und Ort gleich sind.

Im ersten Schritt erstellen wir uns eine Abfrage, die uns alle Sätze liefert, für die diese Kombination mehrfach vorkommt.

select * from #kunde a

where 1 < ( select count(*) from #kunde i where a.vorname = i.vorname and a.nachname = i.nachname and a.ort = i.ort )

Der Subselect in der WHERE-Klausel muss hier als zweiter Vergleichswert genommen und in Klammern gesetzt werden, damit der SQL-Interpreter zurecht kommt. Diese Selektion reicht aber zum Löschen der doppelten Sätze noch nicht aus, da jetzt alle gelöscht würden, die doppelte haben. Es soll aber gerade immer einer stehen bleiben. Man braucht also ein weiteres Kriterium, in dem die Sätze sich unterscheiden. Dazu kann man ein hoffentlich vorhandenes Schlüsselfeld benutzen.

select * from #kunde a

where 1 < ( select count(*) from #kunde i where a.vorname = i.vorname and a.nachname = i.nachname and a.ort = i.ort ) and kunde_id >
(
select min(kunde_id)
from #kunde i
where a.vorname = i.vorname
and a.nachname = i.nachname
and a.ort = i.ort
)

Mit dieser Variante bekommt man alle Duplikate, jeweils ohne den Satz mit dem niedrigsten Schlüsselwert jeder Gruppe. Ist kein Schlüsselfeld vorhanden, so kann man auch die relative Satznummer verwenden. Nach Austausch der Selektion gegen die DELETE-Anweisung werden alle überzähligen Duplikate gelöscht.

delete from #kunde a

where 1 < ( select count(*) from #kunde i where a.vorname = i.vorname and a.nachname = i.nachname and a.ort = i.ort ) and rrn(a) >
(
select min(rrn(a))
from #kunde i
where a.vorname = i.vorname
and a.nachname = i.nachname
and a.ort = i.ort
)

In der Löschvariante wird die relative Satznummer statt eines Schlüsselfeldes verwendet. Diese Selektion lässt sich auch fest als View hinterlegen, sodass die DELETE-Anweisung dann sehr einfach wird.

create view loeschmich as

select * from #kunde a
where 1 < ( select count(*) from #kunde i where a.vorname = i.vorname and a.nachname = i.nachname and a.ort = i.ort ) and kunde_id >
(
select min(kunde_id)
from #kunde i
where a.vorname = i.vorname
and a.nachname = i.nachname
and a.ort = i.ort
)

Auf diese View schrumpft die Löschanweisung auf das elementare Statement „DELETE FROM loeschmich“. Die bisher bearbeitete Variante wird rekursiv ausgeführt, wegen des Rückbezuges im Subselect auf das äußere Select, was bei großen Datenbeständen zu ungünstigem Laufzeitverhalten führen kann. Die Rekursion lässt sich vermeiden mit einer anderen Logik. Ich habe dazu für das Beispiel eine kleine View angelegt, die die Schlüsselfelder, der zu löschenden Sätze liefert.

create view kundedel as

select kunde_id
from #kunde a,
(
select vorname, nachname, ort
from #kunde i
group by vorname, nachname, ort
having count(*) > 1
) as t
where a.vorname = t.vorname
and a.nachname = t.nachname
and a.ort = t.ort
and kunde_id >
(
select min(kunde_id)
from #kunde z
where z.vorname = a.vorname
and z.nachname = a.nachname
and z.ort = a.ort
group by vorname, nachname, ort
)

Hier wird eine Join-Operation zum Ersetzen der Rekursion verwendet. Um diese Operation performant zu machen, empfiehlt es sich, einen entsprechenden Zugriffspfad sicher zu stellen. Dies geschieht mit der Erstellung eines passenden Indexes.

CREATE INDEX kundedup

ON #KUNDE
(
VORNAME,
NACHNAME,
ORT
)

Das Löschen der Duplikate erfolgt jetzt mittels einer einfachen SQL-Anweisung unter Verwendung einer IN-Klausel.

delete from #kunde

where kunde_id in
(
select * from kundedel
)

Nach SQL-Standard geht dies auch ohne Erstellung einer View mittels einer geschachtelten SQL-Konstruktion, die bei meinem PTF-Stand allerdings zu Interpretationsschwierigkeiten des SQL-Interpreters führte.

delete from #kunde

where kunde_id in
(
select kunde_id
from #kunde a,
(
select vorname, nachname, ort
from #kunde i
group by vorname, nachname, ort
having count(*) > 1
) as t
where a.vorname = t.vorname
and a.nachname = t.nachname
and a.ort = t.ort
and kunde_id >
(
select min(kunde_id)
from #kunde z
where z.vorname = a.vorname
and z.nachname = a.nachname
and z.ort = a.ort
group by vorname, nachname, ort
)
)

Neben ein wenig SQL kann man an diesem Beispiel auch lernen, dass SQL auf der AS/400 mittlerweile so richtig Spaß macht, selbst dann, wenn die Qualität und Performance des Datenbanksystems noch nicht immer Schritt hält mit dem Stand der Technik, wie man ihn von anderen Datenbankimplementierungen gewohnt ist. Da ist sicher noch Handlungsbedarf seitens des Herstellers, wenn die AS/400 als reiner Datenbankserver weiter Fuß fassen will.

Den Autor Dieter Bender erreichen Sie unter dieter.bender@midrangemagazin.de