[mySQL] Subquery: You can't specify target table for update in FROM clause

[tedlemegba]

Hallo!

Ich hab mich gerade an meiner ersten Query mit Subquery in mySQL erfreut - sie geht/ging sogar!

SELECT * FROM `cinemaxx` WHERE id < (SELECT id FROM `cinemaxx` ORDER BY id DESC LIMIT 25, 1)

Man stelle das SELECT * gerne als böse hin, ist hier aber unwichtig, weil es nur um die Demonstration der Funktionalität geht.

Damit bezwecken will ich ein Auslesen aller Einträge bis auf die 25 neuesten. Komischerweise funktioniert die Query als Abfrage mit einem SELECT * auch.

Allerdings ist mein eigentliches Ziel nicht das Auslesen, sondern das Löschen dieser Einträge.

Schwuppdiwupp umgeschrieben:

DELETE FROM `cinemaxx` WHERE id < (SELECT id FROM `cinemaxx` ORDER BY id DESC LIMIT 25, 1)

Dooferweise meckert mySQL dann:

#1093 - You can't specify target table 'cinemaxx' for update in FROM clause​

Wieso kann ich mir leider nicht erklären. Der Fehler macht in meinen Gedanken keinen Sinn. Google spuckt nichts für mich Hilfreiches an Informationen raus (zumindest nicht deutschsprachig).

Kann mir wer helfen? Danke!

 

[zerafin]

Ich kann dir nur den Eintrag im Handbuch nennen:
https://dev.mysql.com/doc/refman/5.1/de/subquery-errors.html

Da steht auch nur das es definitiv nicht geht. Aber eine Erklärung finde ich dort auch nicht...

Sie können eine Unterabfrage zur Zuweisung innerhalb einer UPDATE-Anweisung verwenden, da Unterabfragen in UPDATE- und DELETE-Anweisungen ebenso zulässig sind wie in SELECT-Anweisungen. Allerdings können Sie nicht dieselbe Tabelle (in diesem Fall Tabelle t1) sowohl in der FROM-Klausel der Unterabfrage als auch als Aktualisierungsziel angeben.

Gruß, Zera

 

[tedlemegba]

Ich kann dir nur den Eintrag im Handbuch nennen:
https://dev.mysql.com/doc/refman/5.1/de/subquery-errors.html

Da steht auch nur das es definitiv nicht geht. Aber eine Erklärung finde ich dort auch nicht...

Tatsache. Danke!

Na dann.. werd ich mich wohl mal nach anderen Lösungen schlau machen.
Alles in einer Query wäre allerdings schon schön. Einen PHP-Workaround könnte man später immer noch schreiben.

Edit: eventuell geht es mit einem LEFT/INNER JOIN.. *grübel* Effizient? Eher nicht denke ich.

 

[tedlemegba]

Gefundene Ansätze, die aber alle nicht gehen:

DELETE FROM tablename LIMIT 49, 999999​

LIMIT hat hier so auch nur Erfolg be einer SELECT-Query.

DELETE FROM tablename ORDER BY datum ASC LIMIT (SELECT COUNT(*) FROM DATUM - 5)​

War wohl auch nichts, aber netter Versuch. Sowas hab ich mir auch schon überlegt, aber selbst Aggregatfunktionen scheinen da nicht zu funktionieren...


Mal schauen inwiefern da was geht mit Variablen in mySQL.. damit das zumindest in einem Abfrageblock gegessen ist. Ob es geht.. keine Ahnung. Allerdings tendiere ich dann sonst doch eher zum PHP-Workaround.

https://www.gamedev.net/community/forums/topic.asp?topic_id=377202
https://www.flashforum.de/forum/showthread.php?t=193409

 

[zerafin]

Vielleicht helfen dir meine (nicht ausgesponnenen) Gedanken etwas weiter: Wären wir in Oracle könnte man das schön über eine Schattentabelle (Stichwort: Materialized View) lösen. Die gibt es in Mysql zwar nicht, aber man könnte selber eine bauen.

Falls diese Abfrage nicht zu oft laufen soll, kannst du es zumindest über drei SQL Abfragen hintereinander lösen, statt einem PHP-Workaround.

Schematisch so:

TRUNCATE `cinemaxx_tmp`

INSERT INTO `cinemaxx_tmp`
SELECT DISTINCT id FROM `cinemaxx` ORDER BY id DESC LIMIT 25, 1

DELETE FROM `cinemaxx` WHERE id < (SELECT id FROM `cinemaxx_tmp`)

Vielleicht hilft dir das als Denkansatz, je nachdem wie oft die Abfrage laufen soll.

Gruß, Zera

 

[tedlemegba]

Vielleicht helfen dir meine (nicht ausgesponnenen) Gedanken etwas weiter: Wären wir in Oracle könnte man das schön über eine Schattentabelle (Stichwort: Materialized View) lösen. Die gibt es in Mysql zwar nicht, aber man könnte selber eine bauen.

Falls diese Abfrage nicht zu oft laufen soll, kannst du es zumindest über drei SQL Abfragen hintereinander lösen, statt einem PHP-Workaround.

Hehe danke.
Dachte auch schon an eine temporäre Auslagerungstabelle.

Wo du gerade ansprichst wie oft man das braucht.

Aber ich habe mich eben entschieden, dass ich es doch anders machen werde. Werde bei jedem Aufruf des Skriptes nur den ältesten Eintrag löschen, weil es eh meistens so ist, dass nur ein neuer (wenn) hinzukommt und ich die Anzahl der Einträge einigermaßen konstant halten kann. Oder doch der PHP-Workaround. *grübel*

Danke dir. ^^

 

[ice-breaker]

zerafin, MySQL5 unterstützt Views. ich denke mal damit sollte das zu lösen sein oder?

 

[zerafin]

zerafin, MySQL5 unterstützt Views. ich denke mal damit sollte das zu lösen sein oder?

Views ja, aber keine Materialized Views.

Ein View ist ja nur eine Art Alias auf eine Select-Abfrage, deswegen nehme ich an, dass mit einem View die selbe Fehlermeldung kommen würde. (nicht getestet) Mit einem Materialized View (also eine regelmäßig automatisch befüllte View) wäre das Problem zu umgehen, da das Befüllen des View und die Update/Delete Anweisung zwei voneinander unabhängige Transaktionen wären.

Gruß, Zera

 

[ice-breaker]

Views ja, aber keine Materialized Views.

Ein View ist ja nur eine Art Alias auf eine Select-Abfrage, deswegen nehme ich an, dass mit einem View die selbe Fehlermeldung kommen würde. (nicht getestet) Mit einem Materialized View (also eine regelmäßig automatisch befüllte View) wäre das Problem zu umgehen, da das Befüllen des View und die Update/Delete Anweisung zwei voneinander unabhängige Transaktionen wären.

Gruß, Zera

interessant, ja sowas wäre in mysql net
(aber da wir schon so lange auf stored procedures und trigger warten mussten, wir das das wohl nie was)

 

[bartman]

Ohne je Subquerys genutzt zu haben:

SELECT * FROM `cinemaxx` WHERE id < ANY (SELECT id FROM `cinemaxx` ORDER BY id DESC LIMIT 25, 1)

laut https://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html sollte das stimmen

 

[tedlemegba]

Ohne je Subquerys genutzt zu haben:

SELECT * FROM `cinemaxx` WHERE id < ANY (SELECT id FROM `cinemaxx` ORDER BY id DESC LIMIT 25, 1)

laut https://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html sollte das stimmen

Hehe.. wäre wohl ein Treffer gewesen.

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' ​

Hier läuft soweit ich das sehen kann MySQL 5.0.24a.
Die Leute mit mySQL 4 haben eh ein Problem oder?

 

[bartman]

Hmm schade, eigentlich sollte das mit allem >5 gehen

 

[strolch00]

DELETE FROM `cinemax` ORDER by ID DESC LIMIT >>100<<

>>100<< mit deiner Anzahl ersetzen

* edit hab überlesen das LIMIT nicht geht sorry, ich habs bei mir auf ner 4er MAX getestet und es funzt. Wobei ich mit dem regulären LIMIT 0, 100 probs hatte vielleicht kann mir das ja mal einer erklären.

 

[theHacker]

hab überlesen das LIMIT nicht geht sorry, ich habs bei mir auf ner 4er MAX getestet und es funzt. Wobei ich mit dem regulären LIMIT 0, 100 probs hatte vielleicht kann mir das ja mal einer erklären.

Das

Manual kanns dir erklären

The MySQL-specific LIMIT row_count option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a given DELETE statement does not take too much time. You can simply repeat the DELETE statement until the number of affected rows is less than the LIMIT value.

Quelle: https://dev.mysql.com/doc/refman/5.1/en/delete.html

 

[strolch00]

Logisch