Trable s MySQL - select v poddotazu

Dnes jsem potřeboval upravit strukturu již existující tabulky v MySQL databázi, ve které bylo přibližně milion záznamů. Konkrétně bylo potřeba přidat unikátní klíč pro dva sloupce v InnoDB, problém byl v tom že některé řádky nebyly unikátní. Řešení se ze začátku zdálo jednoduché, ale MySQL umí pěkně zamotat hlavu a tak bylo potřeba trochu hledat na internetu.

Výchozí stav

Mějme v MySQL databázi tabulku

CREATE TABLE `my_table`
(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `data2` INT UNSIGNED  NOT NULL,
    `data1` INT UNSIGNED NOT NULL,
    --
    PRIMARY KEY (`id`),
    FOREIGN KEY (`data1`) REFERENCES `another_table1` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`data2`) REFERENCES `another_table2` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARSET=utf8 COMMENT='Example table';

Potřebujeme přidat unikátní klíč pro 2 sloupce. Za normálních okolností (pokud by databáze byla prázdná) by stačilo spustit sql dotaz

alter table my_table add UNIQUE KEY (`data1`, `data2`);

Jelikož je v databázi spousta dat a některé klíče nejsou unikátní, volání uvedeného sql dotazu selhalo s chybou

ERROR 1062 (23000): Duplicate entry '2008-08-23-2-7' for key 2

Problém

Proto jsem se rozhodl duplikátní řádky smazat. A tady už nastává problém.

DELETE FROM my_table WHERE id NOT IN (SELECT id FROM my_table GROUP BY data1, data2);

Výše uvedený dotaz by měl smazat všechny řádky, které při alteru tabulky porušují integritu pro unikátní klíč. A tady nastává problém, protože v podmínce (WHERE) příkazu je třeba určit hodnotu podle dotazu na tu samou tabulku. 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 ‘my_table′ for update in FROM clause

Řešení problému

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? Řešením je mezitabulka test:

DELETE FROM my_table WHERE id NOT IN (SELECT id FROM (SELECT id FROM my_table GROUP BY data1, data2) test);

Sice mi takové řešení přijde ujeté a zbytečně komplikující, ale funguje to...

Vyšlo 16.12.2008, v blogu: 0 1 2 3 4 5 6 7 8

Děkuji, že jste se rozhodl(a) přečíst tento článek. Budu rád i za komentář. Pokud Vás tento článek zaujal a rádi byste jej doporučili ostatním, podpořte mně prosím tím, že věnujete minutku svého času a uděláte mi reklamu na linkuj.cz, vybrali.sme.sk či jagg.cz. Přeji příjemné čtení

Poslední články

• Jak fotit kapky vody
• Druhý milion návštěvníků
• Výběr digitální zrcadlovky - Nikon D40
• Nemovitosti na prodej
• Mobilní telefony Samsung raději nekupovat
• Veselé Vánoce a PF 2009
• Dokument Uloupené kosovo
• Trable s MySQL - select v poddotazu
• Vánoční dárky letos z klidu domova
• Kde najít obchody C&A? Móda, oděvy, oblečení
• Recept na bábovku
• seriál Červený trpaslík - Red dwarf
• Brněnský hantec
• Zamyšlení nad vývojem SW
• Gothic 3

Diskuse k blogu