MySQL-Fehler 1054, (angeblich!) fehlende Spalte

[Rolf B]

Hallo Auge,

bin gespannt, was deine Prüfung auf "war's die richtige DB" ergibt…

Unabhängig davon: ich selbst hätte die Query wohl anders formuliert. Ob das dann besser ist, weiß ich natürlich auch nicht 😉. Aber vielleicht ist es lesbarer und besser verständlich.

Folgende Query liefert mir alle Filenames, die mehr als einmal verwendet werden, sowie die kleinste verwendete ID dazu:

select min(id) as lead_id, filename
from tbl_uploads
group by filename
having count(*) > 1

Wenn's einen Index auf (filename,id) gibt, ist das eine reine Indexoperation und sollte in O(n) laufen.

Das kann ich nun mit den Uploads joinen. Um in einem JOIN zu löschen, hat MySQL eine spezielle Multi-Table DELETE Syntax, bei der man hinter dem DELETE die am Join beteiligten Tabellen auflistet, in denen die gefundenen Sätze zu löschen sind. Da hier mit Aliasnamen gearbeitet wird, ist (sagt ein Stackoverflow-Treffer) hinter DELETE der Aliasname anzugeben, nicht der Tablename.

Innnerhalb der Table-Expression hinter dem Join brauchst Du meines Wissens keine Table-Aliase. Und Backticks brauchst Du eigentlich gar nicht, aber ich hab sie mal mit eingesetzt.

DELETE `t1`
FROM `tbl_uploads` AS `t1`
JOIN (
         SELECT MIN(`id`) AS `lead_id`, `filename`
         FROM `tbl_uploads`
         GROUP BY `filename`
         HAVING count(*) > 1
     ) `t2`
ON `t1`.`filename` = `t2`.`filename` 
   AND `t1`.`id` > `t2`.`lead_id`

Das geht in MySQL 5.7, sollte daher auch in MySQL 8.* oder im Datenmariechen funktionieren. Die Syntax für DELETEs aus Joins kann von SQL-Engine zu SQL-Engine wechseln oder auch gar nicht unterstützt sein.

Ausprobiert habe ich es nicht. Aber es sollte performanter und auch lesbarer sein als deine IN-Abfrage in einen Join hinein; ich würde eine O(n²) Komplexität unterstellen, bei deiner Query hingegen O(n³). Ohne den Index auf filename sind es allerdings viele Tablescans, was die Sache ausbremst. Ob der Index wirklich was bringt, kommt drauf an, wieviele Sätze du in der Table hast.

Den inneren SELECT könnte man auch als CTE (common table expression) vor die Query ziehen, aber CTEs gibt's in MYSQL 5 noch nicht und ich weiß nicht, was Du verwendest.

An Stelle von JOIN gibt es auch Syntaxen (Syntaxis?), die mit SQL Tupeln arbeiten und dann wieder den IN Operator verwenden, aber das ist (a) längst nicht in jedem SQL möglich und (b) muss hier ja der Filename auf Gleichheit und die ID auf größer (oder Ungleich) verglichen werden, weil Du ja genau eine ID beibehalten willst.

Rolf