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ů.

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

Vložit komentář

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:

avatar © 2005-2018 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.