• Sie befinden sich hier: Startseite > SQL-Backstube > Dreiertausch mit SQL

Die SQL-Backstube

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

12.02.2011: Dreiertausch mit SQL

Unter dem Stichwort "Column-Swap" - dabei werden die Inhalte zweier Spalten mit einem SQL-UPDATE getauscht - finden sich schon viele Beiträge zu den SQL-Datenbanken. Interessant ist, dass sich die Datenbanken (mal wieder) unterschiedlich verhalten.

Getestet mit folgender Datenbank:

* IBM DB2 9.1
* MySQL 5.1.55
* ORACLE 10g 10.2 EE

Testdaten einfügen und Column-Swap ausführen MySQL

Listing 1:

1. CREATE TABLE column_swap (
2. id INT NOT NULL,
3. wert1 INT NOT NULL,
4. wert2 INT NOT NULL,
5. PRIMARY KEY (id)
6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Listing 2:

1. INSERT INTO column_swap VALUES
2. ( 1, 11, 111),
3. ( 2, 22, 222),
4. ( 3, 33, 333);
6. UPDATE column_swap
7. SET wert1 = wert2,
8. wert2 = wert1;
9. Query OK, 3 rows affected (0.00 sec)
10. Rows matched: 3 Changed: 3 Warnings: 0

Der INSERT ist korrekt ausgeführt, der UPDATE auch. aber was enthält nun die Tabelle? Schauen wir einfach mal nach.

Was enthält nun die Tabelle?

Listing 3:

1. SELECT * FROM column_swap;
2. +----+-------+-------+
3. | id | wert1 | wert2 |
4. +----+-------+-------+
5. | 1 | 111 | 111 |
6. | 2 | 222 | 222 |
7. | 3 | 333 | 333 |
8. +----+-------+-------+
9. 3 rows in set (0.00 sec)

Upps, nicht unbedingt das erwünschte Ergebnis, aber eventuell das erwartete aus Sicht eines Programmierers. Klar, oder etwa doch nicht ...?

[Zitat]
Einstiegsaufgaben:
1.) Du hast zwei Variablen a und b. Schreib einige Programmzeilen, sodass die beiden ihren Wert tauschen. Dann schreib ein paar Zeilen, damit a die kleinere Zahl enthält. In der Regel wird in C oder Pascal von den Schülern zuerst meist die Idee a=b und b=a geboren, aber rasch wieder verworfen. Dann folgt der bekannte Dreiertausch x=a; a=b; b=x.

Was kann Python, was viele andere nicht können? Die Mehrfachzuweisung a,b=b,a.
Quelle: www.hib-wien.at/leute/wurban/informatik/sortieren/sort0_intro.pdf
[/Zitat]

Also ist es doch nicht so klar (siehe Python) wie so ein typischer Dreiertausch oder "Mehrfachzuweisung" ablaufen muss.

Lösung für MySQL

Bei MySQL geht beispielsweise dies. Einfach die selbe Tabelle mit zwei verschiedenen Alias einbinden und schon wird der "Column-Swap" wie gewünscht ausgeführt.

Listing 4:

1. UPDATE column_swap t1,
2. column_swap t2
3. SET t1.wert1 = t2.wert2,
4. t1.wert2 = t2.wert1
5. WHERE t1.id = t2.id;
6. Query OK, 3 rows affected (0.00 sec)
7. Rows matched: 3 Changed: 3 Warnings: 0
9. SELECT * FROM column_swap;
10. +----+-------+-------+
11. | id | wert1 | wert2 |
12. +----+-------+-------+
13. | 1 | 111 | 11 |
14. | 2 | 222 | 22 |
15. | 3 | 333 | 33 |
16. +----+-------+-------+
17. 3 rows in set (0.00 sec)

Wie sieht das Ganze nun bei IBM DB2 aus?

Listing 5:

1. CREATE TABLE column_swap (
2. id INT NOT NULL,
3. wert1 INT NOT NULL,
4. wert2 INT NOT NULL,
5. PRIMARY KEY (id)
6. );
8. INSERT INTO column_swap VALUES
9. ( 1, 11, 111),
10. ( 2, 22, 222),
11. ( 3, 33, 333);
13. UPDATE column_swap
14. SET wert1 = wert2,
15. wert2 = wert1;
17. SELECT * FROM column_swap;
18. +----+-------+-------+
19. | ID | WERT1 | WERT2 |
20. +----+-------+-------+
21. | 1 | 111 | 11 |
22. | 2 | 222 | 22 |
23. | 3 | 333 | 33 |
24. +----+-------+-------+

Schon mit dem einfachen UPDATE erhalten wir das gewünschte Ergebnis. DB2 hat einen anderen Werte- oder Variablen "SCOPE" als MySQL implementiert. Was ist nun mit dem "Multi-Table-Insert"?

Listing 6:

1. UPDATE column_swap t1,
2. column_swap t2
3. SET t1.wert1 = t2.wert2,
4. t1.wert2 = t2.wert1
5. WHERE t1.id = t2.id;
7. SQL0104N Auf "PDATE column_swap t1" folgte das unerwartete Token ",". Zu den
8. möglichen Token gehören: "JOIN". SQLSTATE=4260

Die etwas kryptische Fehlermeldung soll wohl besagen, dass ein Multi-Table-Update bei DB2 nicht erlaubt ist. Eigentlich erfolgt auch gar kein UPDATE auf zwei Tabellen, sondern nur auf die Tabelle "column_swap t1", die Inhalte der Tabelle "column_swap t2" werden im Prinzip nicht geändert.

..und nun noch ORACLE..

Listing 7:

1. CREATE TABLE column_swap (
2. id INT NOT NULL,
3. wert1 INT NOT NULL,
4. wert2 INT NOT NULL,
5. PRIMARY KEY (id)
6. );
8. INSERT INTO column_swap VALUES ( 1, 11, 111);
9. INSERT INTO column_swap VALUES ( 2, 22, 222);
10. INSERT INTO column_swap VALUES ( 3, 33, 333);
12. UPDATE column_swap
13. SET wert1 = wert2,
14. wert2 = wert1;
16. SELECT * FROM column_swap;
17. +----+-------+-------+
18. | ID | WERT1 | WERT2 |
19. +----+-------+-------+
20. | 1 | 111 | 11 |
21. | 2 | 222 | 22 |
22. | 3 | 333 | 33 |
23. +----+-------+-------+

Hier liefert ebenfalls der einfache UPDATE das gewünschte Ergebnis. Was ist hier mit dem Multi-Table-Update?

Listing 8:

1. UPDATE column_swap t1,
2. column_swap t2
3. SET t1.wert1 = t2.wert2,
4. t1.wert2 = t2.wert1
5. WHERE t1.id = t2.id;
7. ORA-00971: Schlüsselwort SET fehlt

Auch ORACLE kommt mit dem "Multi-Table-Update" nicht zurecht. Laut SQL-Standard SQL:1999 kann ein UPDATE auch nur auf eine Tabelle auf einmal erfolgen.

Resumee

Wieder zeigt sich, das MySQL einen Sonderweg eingeschlagen hat, um einen eventuellen Performancegewinn beim UPDATE zu erzielen. Ob dieser Sonderweg sinnvoll und praktikabel ist, kann jeder für sich selbst entscheiden. Wer jedoch einmal den Datenbankhersteller wechseln möchte, sollte diesen Unterschied kennen und berücksichtigen.

https://twiedmann.de