Mehrere Einträge anhand Primary-Key-Liste löschen

Hallo!

Ich lausche schon seit längerem dieser interessanten Mailinglist, weil ich
selber viel mit PostgreSQL arbeite und so immer wieder auf interessante
Tipps und Tricks stoße. Bis jetzt konnte ich all meine Probleme selber
lösen, aber jetzt stehts bei mir grad an:

Ich arbeite für eine kleine Bibliothek an einer Webapplikation, die soweit
bereits seit einigen Monaten gut auf Basis von PostgreSQL läuft. Es gibt
u.a. eine Tabelle mit Leserstammdaten (Leser), wo unter anderem das
Geburtsdatum gespeichert ist. In einer anderen Tabelle sind alle
Definitionen für Abonnements hinterlegt (LAbo), und in einer dritten Tabelle
verknüpfe ich diese Daten (LxAbo).

Die verschiedenen Abonnements müssen zu verschiedenen Zeitpunkten wieder
rausgelöscht werden (z.B. am Ende des Jahres, oder am Ende eines
Halbjahres). Es gibt aber auch ein spezielles Abonnement für
Kindergarten-Kinder, wo das Abonnement erst rausgelöscht werden darf, wenn 2
Bedingungen zutreffen: 1. muss es Ende Juli eines Jahres sein, und 2. muss
das Kind zu dem Zeitpunkt mind. 6 Jahre alt sein.

Jetzt meine Frage: Brauche ich dafür tatsächlich mehrere Abfragen, die ich
hintereinander ausführen muss, oder lässt sich das schon in einem Aufruf
eleganter erledigen? Soweit bin ich ungefähr:

=============================================

SELECT lxabo."LxAboID”

FROM "Leser" AS l, "LxAbo" AS lxabo

WHERE ((((EXTRACT(YEAR FROM CURRENT_DATE)) - (EXTRACT(YEAR FROM
l."LGebDat")) -

(EXTRACT(DOY FROM CURRENT_DATE)<EXTRACT(DOY FROM
l."LGebDat"))::integer) >= 6) AND

(l."LID" = lxabo."LxAboLID") AND

(lxabo."LxAboLAboID" = 'testAboID') AND

(lxabo."LxAboBibID" = '40922002321'));

=============================================

ergibt korrekt:

=============================================

"f3f35a20b9985af4a46c2db8faeaeb3b“

"2a1447bd485163afc3031930124ed504"

usw.

=============================================

Ich bekomme also eine Liste von LxAboIDs, die ich jetzt alle in der Tabelle
LxAbo rauslöschen müsste. Aber geht das direkt in PostgreSQL, oder muss ich
das Ergebnis mit PHP auslesen und kann die Einträge dann erst aus der
Tabelle löschen?

Vielen Dank, Stefan

Hey Stephan,

Schau die mal subselects an, damit sollte das kein Problem sein ;)

Lg,
Michael

Sent from my iPhone

On 21.07.2012, at 15:18, "Mag. Dr. Stefan Krejci" <office(at)skrejci(dot)com> wrote:

> Hallo!
>
> Ich lausche schon seit längerem dieser interessanten Mailinglist, weil ich selber viel mit PostgreSQL arbeite und so immer wieder auf interessante Tipps und Tricks stoße. Bis jetzt konnte ich all meine Probleme selber lösen, aber jetzt stehts bei mir grad an:
>
> Ich arbeite für eine kleine Bibliothek an einer Webapplikation, die soweit bereits seit einigen Monaten gut auf Basis von PostgreSQL läuft. Es gibt u.a. eine Tabelle mit Leserstammdaten (Leser), wo unter anderem das Geburtsdatum gespeichert ist. In einer anderen Tabelle sind alle Definitionen für Abonnements hinterlegt (LAbo), und in einer dritten Tabelle verknüpfe ich diese Daten (LxAbo).
>
> Die verschiedenen Abonnements müssen zu verschiedenen Zeitpunkten wieder rausgelöscht werden (z.B. am Ende des Jahres, oder am Ende eines Halbjahres). Es gibt aber auch ein spezielles Abonnement für Kindergarten-Kinder, wo das Abonnement erst rausgelöscht werden darf, wenn 2 Bedingungen zutreffen: 1. muss es Ende Juli eines Jahres sein, und 2. muss das Kind zu dem Zeitpunkt mind. 6 Jahre alt sein.
>
> Jetzt meine Frage: Brauche ich dafür tatsächlich mehrere Abfragen, die ich hintereinander ausführen muss, oder lässt sich das schon in einem Aufruf eleganter erledigen? Soweit bin ich ungefähr:
>
> =============================================
> SELECT lxabo."LxAboID”
> FROM "Leser" AS l, "LxAbo" AS lxabo
> WHERE ((((EXTRACT(YEAR FROM CURRENT_DATE)) - (EXTRACT(YEAR FROM l."LGebDat")) -
> (EXTRACT(DOY FROM CURRENT_DATE)<EXTRACT(DOY FROM l."LGebDat"))::integer) >= 6) AND
> (l."LID" = lxabo."LxAboLID") AND
> (lxabo."LxAboLAboID" = 'testAboID') AND
> (lxabo."LxAboBibID" = '40922002321'));
> =============================================
>
> ergibt korrekt:
>
> =============================================
> "f3f35a20b9985af4a46c2db8faeaeb3b“
> "2a1447bd485163afc3031930124ed504"
> usw.
> =============================================
>
> Ich bekomme also eine Liste von LxAboIDs, die ich jetzt alle in der Tabelle LxAbo rauslöschen müsste. Aber geht das direkt in PostgreSQL, oder muss ich das Ergebnis mit PHP auslesen und kann die Einträge dann erst aus der Tabelle löschen?
>
> Vielen Dank, Stefan
>
>

Hallo,

Am 21.07.2012 15:18, schrieb Mag. Dr. Stefan Krejci:
>
> =============================================
>
> SELECT lxabo."LxAboID"
>
> FROM "Leser" AS l, "LxAbo" AS lxabo
>
> WHERE ((((EXTRACT(YEAR FROM CURRENT_DATE)) - (EXTRACT(YEAR FROM
> l."LGebDat")) -
>
> (EXTRACT(DOY FROM CURRENT_DATE)<EXTRACT(DOY FROM
> l."LGebDat"))::integer) >= 6) AND
>
> (l."LID" = lxabo."LxAboLID") AND
>
> (lxabo."LxAboLAboID" = 'testAboID') AND
>
> (lxabo."LxAboBibID" = '40922002321'));
>
> =============================================
>
> ergibt korrekt:
>
> =============================================
>
> "f3f35a20b9985af4a46c2db8faeaeb3b"
>
> "2a1447bd485163afc3031930124ed504"
>
> usw.
>
> =============================================
>
> Ich bekomme also eine Liste von LxAboIDs, die ich jetzt alle in der
> Tabelle LxAbo rauslöschen müsste. Aber geht das direkt in PostgreSQL,
> oder muss ich das Ergebnis mit PHP auslesen und kann die Einträge dann
> erst aus der Tabelle löschen?
>

Wenn Du die WHERE-Bedingung statt in eine SELECT in eine DELETE packst,
werden die Daten
gleich aus der Datenbank gelöscht. Aber vorsicht - es gibt kein zurück.
Was weg ist, ist weg.

Viele Grüße,

Susanne

--
Susanne Ebrecht,
Bielefeld

Mag. Dr. Stefan Krejci <office(at)skrejci(dot)com> wrote:

Damit willst das aktuelle Alter bestimmen, oder?

>   WHERE ((((EXTRACT(YEAR FROM CURRENT_DATE)) - (EXTRACT(YEAR FROM l."LGebDat"))
> -
>
>           (EXTRACT(DOY FROM CURRENT_DATE)<EXTRACT(DOY FROM
> l."LGebDat"))::integer) >= 6) AND

geht übersichtlicher und einfacher so:

test=*# select extract(year from age('2007-07-28'::date));
date_part
-----------
4
(1 row)

Das '2007-07-28'::date ist das fragliche Datum, Geburtstag.

Welche PG-Version ist am Start? Mit 9.1 könnte man wCTE verwenden für
Deine eigentliche Abfrage, aber Du hast ja auch so schon passende
Antworten.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Hallo und Danke für eure schnelle Hilfe!

> Schau die mal subselects an, damit sollte das kein Problem sein ;)

Hab ich schon, ich hab auch schon mit CTEs gearbeitet etc. Ich bin trotzdem auf keinen grünen Zweig gekommen – ich fürchte, ich stehe scheinbar total auf der Leitung, weil…

> Wenn Du die WHERE-Bedingung statt in eine SELECT in eine DELETE packst, werden die Daten
> gleich aus der Datenbank gelöscht. Aber vorsicht - es gibt kein zurück. Was weg ist, ist weg.

…wenn ich diesen Tipp von Susanne übersetze, müsste der Befehl lauten:

DELETE

FROM "Leser" AS l, "LxAbo" AS lxabo

WHERE ((EXTRACT(YEAR FROM AGE(l."LGebDat")) >= 6) AND

(l."LID" = lxabo."LxAboLID") AND

(lxabo."LxAboLAboID" = 'testAboID') AND

(lxabo."LxAboBibID" = '40922002321'));

Aber zwei oder mehrere Tabellen lässt PostgreSQL beim DELETE-Befehl nicht zu. Und wenn ich die Leser-Tabelle weg mache (weil ja aus der LxAbo rausgelöscht werden soll), frisst mir der Parser die WHERE-Clause nicht mehr. Susanne, wie müsste der Befehl denn lauten? Bitte-danke!

Danke, Andreas, übrigens für die vereinfachte Alters-Bestimmung ;-) CTEs weiß ich, was sind wCTE? Bei mir läuft aktuell Version 9.0.4 – das Update auf die aktuelle Version möchte ich bald machen, und wäre vermutlich ein weiteres Thema hier, wie ich das korrekt angehe…

> I wüŗde keine Zeile eigentlich loschen,aber nur als ´ inactive´ markieren in ´ relationship table´ .

Danke für den Tipp. Ich lösche die Zeilen aus dieser Tabelle hier trotzdem raus, ein Trigger auf DELETE schreibt mir einen passenden Log-Eintrag dazu.

Liebe Grüße, Stefan

> Aber zwei oder mehrere Tabellen lässt PostgreSQL beim DELETE-Befehl
> nicht zu. Und wenn ich die Leser-Tabelle weg mache (weil ja aus der
> LxAbo rausgelöscht werden soll), frisst mir der Parser die WHERE-Clause
> nicht mehr. Susanne, wie müsste der Befehl denn lauten? Bitte-danke!
>
>
>
> Danke, Andreas, übrigens für die vereinfachte Alters-Bestimmung ;-) CTEs
> weiß ich, was sind wCTE? Bei mir läuft aktuell Version 9.0.4 – das
> Update auf die aktuelle Version möchte ich bald machen, und wäre
> vermutlich ein weiteres Thema hier, wie ich das korrekt angehe…

Doch doch ... man kann in mehr als einer Tabelle gleichzeitig löschen:

test=*# create table foo1 (i int);
CREATE TABLE
test=*# create table foo2 (i int);
CREATE TABLE
test=*# create table foo3 (i int);
CREATE TABLE
test=*# insert into foo1 select * from generate_series(1,10);
INSERT 0 10
test=*# insert into foo2 select * from generate_series(1,10);
INSERT 0 10
test=*# insert into foo3 select * from generate_series(1,10);
INSERT 0 10
test=*# with x1 as (delete from foo1 where i < 5 returning i), x2 as (delete from foo2 where i > 5 returning i), x3 as (delete from foo3 where i = 5 returning i) select * from x1 union all select * from x2 union all select * from x3;
i
----
1
2
3
4
6
7
8
9
10
5
(10 rows)

So, was vermutest Du nun in den einzelnen Tabellen?

test=*# select * from foo1;
i
----
5
6
7
8
9
10
(6 rows)

test=*# select * from foo2;
i
---
1
2
3
4
5
(5 rows)

test=*# select * from foo3;
i
----
1
2
3
4
6
7
8
9
10
(9 rows)

Das writeableCTE bedeutet (ab 9.1), daß Du mit den Returning-Werten weiter arbeiten kannst, neues Beispiel:

test=*# create table foo1 (i int);
CREATE TABLE
test=*# create table foo2 (i int);
CREATE TABLE
test=*# insert into foo1 select * from generate_series(1,10);
INSERT 0 10
test=*# with x1 as (delete from foo1 where i < 5 returning i), x2 as (delete from foo1 where i > 7 returning i) insert into foo2 select * from x1;
INSERT 0 4
test=*# select * from foo2;
i
---
1
2
3
4
(4 rows)

Am 21.07.2012 17:31, schrieb Mag. Dr. Stefan Krejci:
>
>
> …wenn ich diesen Tipp von Susanne übersetze, müsste der Befehl lauten:
>
> DELETE
>
> FROM "Leser" AS l, "LxAbo" AS lxabo
>
> WHERE ((EXTRACT(YEAR FROM AGE(l."LGebDat")) >= 6) AND
>
> (l."LID" = lxabo."LxAboLID") AND
>
> (lxabo."LxAboLAboID" = 'testAboID') AND
>
> (lxabo."LxAboBibID" = '40922002321'));
>
> Aber zwei oder mehrere Tabellen lässt PostgreSQL beim DELETE-Befehl
> nicht zu.
>

Aus wievielen Tabellen willst Du denn Löschen? Du kannst zeitgleich nur
aus einer Löschen.
Die Frage ist, wie Du den Join gestaltest. Möchtest Du aus Leser
löschen, sollte es so aussehen:

DELETE FROM leser
WHERE

((EXTRACT(YEAR FROM AGE(LGebDat)) >= 6) AND

LID in
(SELECT LxAboLID
FROM lxabo
WHERELxAboLAboID = 'testAboID'
ANDLxAboBibID = '40922002321');

Susanne

--
Susanne Ebrecht,
Bielefeld

Sodala, das wars, vielen herzlichen Dank an alle!

Das Stichwort war „IN“ im Beispiel von Susanne. Kannte ich natürlich, hab’s aber mit meiner Problemstellung überhaupt nicht verlinkt – das war also die Leitung, auf der ich stand J…

Die Lösung lautet nun:

DELETE FROM "LxAbo"
WHERE (("LxAboLAboID" = ‘testAboID’) AND
("LxAboBibID" = '40922002321') AND
("LxAboLID" IN ((SELECT "LID" FROM "Leser" AS l WHERE (EXTRACT(YEAR FROM AGE(l."LGebDat"))) >= 6))))

Jetzt werd ich mir die wCTEs zu Gemüte führen…

Danke nochmal, schönen Abend! Stefan