blog.matajon.net

Občas se stává, že potřebujete při mazání nebo upravování záznamu v podmínce (WHERE) příkazu určit hodnotu podle dotazu na tu samou tabulku. Například pokud chci smazat z tabulky všechny články u kterých se titulek v tabulce vyskytuje u více záznamů.

Mějme tedy tabulku -

CREATE TABLE ukazka1 (
    Id INT NOT NULL AUTO_INCREMENT, 
    Title VARCHAR(30) NOT NULL, 
    Content TEXT, 
    Published TIMESTAMP DEFAULT NOW(), 
    PRIMARY KEY(Id)
);

A v ní data -

INSERT INTO ukazka1 (Title, Content) VALUES ("Titulek 1", "Lorem ipsum");
INSERT INTO ukazka1 (Title, Content) VALUES ("Titulek 2", "Teeext");
INSERT INTO ukazka1 (Title, Content) VALUES ("Foo", "Bar");
INSERT INTO ukazka1 (Title, Content) VALUES ("Foo", "Bar2");
INSERT INTO ukazka1 (Title, Content) VALUES ("Titulek 3", "CHRO");
INSERT INTO ukazka1 (Title, Content) VALUES ("Titulek 1", "Opakuje se");

Takže ve výsledku tabulka vypadá asi takto -

mysql> SELECT * FROM ukazka1;
+----+-----------+----------------------------+---------------------+
| Id | Title     | Content                    | Published           |
+----+-----------+----------------------------+---------------------+
|  1 | Titulek 1 | Lorem ipsum                | 2008-03-23 13:38:19 |
|  2 | Titulek 2 | Teeext                     | 2008-03-23 13:38:19 |
|  3 | Foo       | Bar                        | 2008-03-23 13:38:19 |
|  4 | Foo       | Bar2                       | 2008-03-23 13:38:19 |
|  5 | Titulek 3 | CHRO                       | 2008-03-23 13:38:19 |
|  6 | Titulek 1 | Opakuje se                 | 2008-03-23 13:38:20 |
+----+-----------+----------------------------+---------------------+
6 rows in set (0.00 sec)

Tak, a teď jak vymazat záznamy s více než jedním výskytem titulku?
Najdeme je jednoduše příkazem

mysql> SELECT Title FROM ukazka1 GROUP BY Title HAVING COUNT(Title) > 1;
+-----------+
| Title     |
+-----------+
| Foo       |
| Titulek 1 |
+-----------+
2 rows in set (0.00 sec)

Takže čistě logicky by dotaz na smazání takovýchto dotazů měl vypadat tak -

DELETE FROM ukazka1 
WHERE Title IN (
    SELECT Title 
    FROM ukazka1 
    GROUP BY Title 
    HAVING COUNT(Title) > 1
);

Ovšem po spuštění dotazu zjistíme, že takhle to asi nepujde. MySQL nám to oznámí milou chybou “ERROR 1093 (HY000): You can’t specify target table ‘ukazka1′ for update in FROM clause“. Takže nám je jasné, že MySQL nedokáže v poddotazu vybírat data z tabulky ze které chceme mazat nebo ji měnit. Takže jak na to?

Jednoduše, nebudeme vybírat data z té samé tabulky ale z odvozené tabulky (derived table) s poddotazem, konkrétně nějak tak -

1
2
3
4
5
6
7
8
9
10

DELETE FROM ukazka1 
WHERE Title IN (
    SELECT Title 
    FROM (
        SELECT Title 
        FROM ukazka1 
        GROUP BY Title 
        HAVING COUNT(Title) > 1
    ) AS duplicitniZaznamy
);

A tento dotaz už bez problému projde a splní požadovaný úkol -

mysql> DELETE FROM ukazka1 WHERE Title IN (SELECT Title FROM
(SELECT Title FROM ukazka1 GROUP BY Title HAVING COUNT(Title) > 1)
AS duplicitniZaznamy);
Query OK, 4 rows affected (0.04 sec)

mysql> SELECT * FROM ukazka1;
+----+-----------+---------+---------------------+
| Id | Title     | Content | Published           |
+----+-----------+---------+---------------------+
|  2 | Titulek 2 | Teeext  | 2008-03-23 13:38:19 |
|  5 | Titulek 3 | CHRO    | 2008-03-23 13:38:19 |
+----+-----------+---------+---------------------+
2 rows in set (0.00 sec)

Tags: delete, MySQL, poddotaz, update

This entry was posted on Sunday, March 23rd, 2008 at 1:24 pm and is filed under Programování, SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.