• Sie befinden sich hier: Startseite > SQL-Backstube > MySQL und komplexe UPDATE's

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

27.02.2011: MySQL und komplexe UPDATE's

Kaum hat man sich vertippt, muss man schon einen UPDATE machen. Kein Problem, wozu hat man denn eine Datenbank, die einen darin unterstützt, Daten korrekt zu verwalten. Der notwendige Befehl hierfür heisst UPDATE und wird den meisten bekannt sein. Spannend wird das Ganze, wenn der UPDATE auf einem komplexen Sub-Select basiert.

Bei Datenimporten aus Fremdsystemen muss häufig auf Dubletten geprüft werden. Wir bauen also eine kleine Tabelle und validieren die ID auf doppelte. Um keine unnötige Zeit zu verlieren, kommt in die selbe Tabelle noch eine Spalte "dublette", in der die Trefferanzahl der Dublettenprüfung abgelegt werden soll.

Getestet mit folgenden Datenbanken:

* IBM DB2 9.1
* MySQL 5.5.9 (InnoDB)
* MySQL 5.1.30 (MyISAM)
* ORACLE 10g 10.2 EE

Tabellenstrukturen und Testdaten

Listing 1:

1. CREATE TABLE check_dubletten (
2. id INT NOT NULL,
3. dublette INT
4. );
6. INSERT INTO check_dubletten ( id ) VALUES
7. ( 1 ),
8. ( 2 ),
9. ( 2 ),
10. ( 3 ),
11. ( 3 ),
12. ( 3 );
13. Query OK, 6 rows affected (0.02 sec)
14. Records: 6 Duplicates: 0 Warnings: 0

MySQL UPDATE und ERROR 1093 (HY000):

Listing 2:

1. UPDATE check_dubletten cd1
2. SET cd1.dublette = ( SELECT COUNT(*) AS anzahl
3. FROM check_dubletten cd2
4. WHERE cd2.id = cd1.id
5. GROUP BY cd2.id );
6. ERROR 1093 (HY000): You can't specify target table 'cd1' for update in FROM clause

Toll, während dieser UPDATE bei DB2 und ORACLE klaglos funktioniert, kann bei MySQL ein selbstreverenzierender Update basierend auf einer Unterabfrage nicht ausgeführt werden. Dies ist MySQL sehr wohl bekannt und demnach findet sich dort auch ein Workaround.

Damit ich nicht gleich einen "Dr." bekomme, hier schnell der Quellenhinweis dazu: http://www.mysqlfaqs.net/mysql-faqs/Errors/1093-You-can-not-specify-target-table-comments-for-update-in-FROM-clause

..und so sieht die MySQL Lösung aus

Listing 3:

1. UPDATE check_dubletten cd1
2. SET cd1.dublette = ( SELECT anzahl
3. FROM ( SELECT cd2.id, COUNT(*) AS anzahl
4. FROM check_dubletten cd2
5. GROUP BY cd2.id ) cd3
6. WHERE cd3.id = cd1.id );
7. Query OK, 6 rows affected (0.05 sec)
8. Rows matched: 6 Changed: 6 Warnings: 0
10. SELECT id, dublette FROM check_dubletten
11. WHERE dublette > 1;
12. +----+----------+
13. | id | dublette |
14. +----+----------+
15. | 2 | 2 |
16. | 2 | 2 |
17. | 3 | 3 |
18. | 3 | 3 |
19. | 3 | 3 |
20. +----+----------+
21. 5 rows in set (0.00 sec)

Warum geht das nun? Der Sub-Query cd3 materialisiert während der Abarbeitung in MySQL intern als temporäre Tabelle. Wie läßt sich so etwas beweisen? Einfach in dem man einen EXPLAIN ausführt. Da EXPLAIN leider nur mit SELECT funktioniert, muss man eben den UPDATE so ähnlich wie möglich in einen SELECT umschreiben.

Listing 4:

1. EXPLAIN
2. SELECT cd1.dublette
3. FROM check_dubletten cd1
4. JOIN ( SELECT cd2.id, anzahl
5. FROM ( SELECT id, COUNT(*) AS anzahl
6. FROM check_dubletten
7. GROUP BY id ) cd2
8. ) cd3
9. ON cd3.id = cd1.id;
10. +----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
11. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
12. +----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
13. | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | |
14. | 1 | PRIMARY | cd1 | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using join buffer |
15. | 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 3 | |
16. | 3 | DERIVED | check_dubletten | ALL | NULL | NULL | NULL | NULL | 6 | Using temporary; Using filesort |
17. +----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
18. 4 rows in set (0.02 sec)

Und siehe da, bei der Tabelle "check_dubletten" erscheint in der Spalte Extra ein "Using temporary; Using filesort".

Resumee

Der umgeschriebene UPDATE funktioniert jetzt zwar, aber performant kann er nicht sein, da er quasi absichtlich kompliziert gehalten ist, um einen "Bug" in MySQL zu umgehen.

https://twiedmann.de