Poddotaz při UPDATE na stejné tabulce

Školení, která pořádám

V PHP aplikacích poddotazy obvykle nepoužívám a vystačím si se spojováním tabulek. Čas od času je ale použiji při spravování databáze přímo z MySQL konzole. Pro poddotazy při UPDATE a DELETE existuje ale jedno nepříjemné omezení – nelze je pokládat nad tabulkou, kterou měníme. Pokud je tedy potřeba nějakým složitým dotazem (spojujícím více tabulek včetně modifikované) získat seznam řádků k modifikaci a tyto řádky následně změnit, nejde poddotaz použít. Samozřejmě lze vytvořit skript, který tuto operaci provede ve dvou krocích, dá se ale použít i následující trik:

  1. Do proměnné si přiřadíme seznam sloupců k modifikaci: SET @a = (SELECT GROUP_CONCAT(id) FROM tab).
  2. Tuto proměnnou následně použijeme: UPDATE tab SET id = id WHERE FIND_IN_SET(id, @a).

Pro porovnání id se seznamem nelze použít IN, protože i kdyby byl seznam ve tvaru 1,2,3, jedná se o jeden řetězec a ne o seznam čísel. Operátor RLIKE sice nebude nejrychlejší, ale pro jednorázové operace to nevadí.

Pokud složitý dotaz používá GROUP BY, vrátí GROUP_CONCAT jednu hodnotu pro každý řádek. Vyřešit se to dá – jak jinak – poddotazem: SELECT GROUP_CONCAT(id) FROM (SELECT id FROM tab GROUP BY id) tab1.

Při spojování více tabulek se dá použít také syntaxe DELETE tab FROM tab, tab2 WHERE tab.id = tab2.id.

Jakub Vrána, Řešení problému, 31.5.2006, diskuse: 6 (nové: 0)

Diskuse

ikona dgx:

Co třeba přes SELECT vytvořit temporary table

CREATE TEMPORARY TABLE temp_tbl SELECT ... FROM ...

a tu potom využít při UPDATE nebo DELETE.

Dočasné tabulky jsou viditelné pouze pro konkrétní spojení, tak se nemusíme bát třeba konfliktu názvů.

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL: Reakce na: dgx

tomka:

Pokud bude seznam ve tvaru 1,2,3, lze pouzit funkci FIND_IN_SET():

UPDATE tab SET id = id WHERE FIND_IN_SET(id, @a)

ikona Jakub Vrána OpenID:

Díky za upozornění, opravil jsem to.

Pawel:

Už fakt nevím co s tím mám dělat.
Chybu to neháže ale nic to neudělá.

mysql_query("UPDATE login SET nick = '$nick', heslo = '$heslo', email = '$email' WHERE nick = '$nick_prihlas'");

štíhloprd:

aby to melo smysl...
SET @a = (SELECT GROUP_CONCAT(id) FROM tab WHERE upresnujici_podminka);
UPDATE tab SET sloupec = jina_hodnota_nebo_vyraz WHERE FIND_IN_SET(id, @a);

Kajman:

Stačí ten poddotaz dát do spojení místo do where. Delete i update takový postup podporují.

Třeba máme vypočítané postupné jedinečné pořadí podle body ... 1, 2, 3, 4, ale ty řádky, co mají stejné všechny body použité k výpočtu pořadí chceme, aby měly stejné pořadí ... 1, 2, 2, 4.

update tabulka,
       (select body1, body2, body3, min(poradi) poradi
        from   tabulka
        group  by body1, body2, body3
        having count(*) > 1) as vypocet
set    tabulka.poradi = vypocet.poradi
where  tabulka.body1 = vypocet.body1
       and tabulka.body2 = vypocet.body2
       and tabulka.body3 = vypocet.body3
avatar © 2005-2022 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.