Autor Zpráva
Prkny
Profil
Zdravím, prosím o pomoc s elegantnějším řešením za pomoci MySQL.

Mám tři tabulky. Zjednodušeně:
Tabulka Import obsahuje: id, id_zbozi, velikost
Tabulka Sklad obsahuje: id, id_zbozi, velikost
Tabulka Volba obsahuje: id, id_zbozi, velikost, aktivni


Rád bych vzal data z tabulky Import a Sklad pro dané id_zbozi a porovnal (velikost) v tabulce Volba pro dané zboží.
Pokud by existovala shoda, mělo by být v tabulce Volba -> aktivni=1, pokud by shoda neexistovala, byl by potřeba vytvořit nový záznam do tabulky Volba.
Navíc bych potřeboval, aby ostatní záznamy v tabulce Volba, které nejsou v Import a Sklad byly označeny v tabulce Volba = 0

Řeším to ne příliš efektivně v php.


Přikládám příklad:

Tabulka Import (id, id_zbozi, volba):
1,222,36
2,222,38
3,222,40

Tabulka Sklad (id, id_zbozi, volba):
132,222,42

Takto by měla po provedení příkazu vypadat tabulka Volba (id, id_zbozi, velikost, aktivni):
501,222,35,0
502,222,36,1
503,222,37,0
504,222,38,1
505,222,39,0
506,222,40,1
507,222,41,0
508,222,42,1
510,222,43,0

Předem děkuji za pomoc.
tiso
Profil
Prkny:
1. update volba set aktivni = 0;
2. insert into ... on duplicate key update ... aktivni = 0 ..., join na tabuľku import
2. insert into ... on duplicate key update ... aktivni = 0 ..., join na tabuľku sklad
Prkny
Profil
Trošku mám strach, že pokud budu provádět update volba set aktivni = 0; tak v určitých chvílích vypínám všechny volby u produktů, tudíž se může stát, že návštěvník který chytne načtením právě tuto chvíli tak neuvidí u produktu nic.
Nevím jak je to moc pravděpodbné, ale stát se to určitě může.
nemohlo by to jít jinak ?
juriad
Profil
Prkny:
Můžeš celou dávku dotazů provést v transakci; to znamená, že se všechny změny projeví až najednou, jakmile dáš commit.
tiso
Profil
Prkny: druhá možnosť je vynechať prvý update, miesto 1 tam nastavovať 2 a potom na konci odpočítať 1 od všetkých. Len ti to musí ten sĺpec dovoliť a mať to ošetrené v aplikácii. Takže transakcia je lepší spôsob.
Prkny
Profil
Transakce bude nejlepší.
MySQL moc neovládám, poprosil bych o pomoc:

první řádek je jasný
UPDATE test_volba SET aktivni = '0' WHERE id_zbozi='222'

druhý řádek mi při testování již píše chybu
INSERT INTO test_volba (id_zbozi, velikost, aktivni) VALUES ('222',test_import.volba,'1') ON DUPLICATE KEY UPDATE aktivni='1' JOIN test_import on test_volba.id_zbozi=test_import.id_zbozi AND test_volba.velikost=test_import.volba AND test_import.id_zbozi='222'

info o chybě:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN test_import on test_volba.id_zbozi=test_import.id_zbozi AND test_volba.veli' at line 1
tiso
Profil
ON DUPLICATE KEY UPDATE test_volba SET aktivni='1'
lionel messi
Profil
Prkny, tiso:
Odkedy sa čísla dávajú do apostrofov (jasné, prejde to, ale nie je to dobrá praktika)?
Prkny
Profil
tiso: Bohužel stále chyba
INSERT INTO test_volba (id_zbozi, velikost, aktivni) VALUES ('222',test_import.volba,'1') 
ON DUPLICATE KEY UPDATE test_volba SET aktivni='1' 
JOIN test_import ON 
test_volba.id_zbozi=test_import.id_zbozi 
AND test_volba.velikost=test_import.volba 
AND test_import.id_zbozi='222' 

MySQL hlásí: Dokumentace
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET aktivni='1'
JOIN test_import ON
test_volba.id_zbozi=test_import.id_zbozi' at line 2
juriad
Profil
Prkny:
V případě ON DUPLICATE KEY UPDATE není možné takto snadno použít JOIN a ty ho ani nepotřebuješ.
INSERT INTO test_volba (id_zbozi, velikost, aktivni)
  SELECT ti.id_zbozi, ti.volba, '1' FROM test_import ti
ON DUPLICATE KEY UPDATE aktivni = '1'
Prkny
Profil
juriad:
Ten příkaz musí být proveden pouze u zboží v našem příkladě id_zbozi = 222.
Tvůj příkaz mi udělal to, že přidal do tabulky test_volba všechny záznamy které obsahovala tabulka test_import a nastavil u všech záznamů aktivní = 1
juriad
Profil
Prkny:
Aha, tak si na konec druhého řádku přidej WHERE podmínku. O tom omezení na jeden záznam jsi se totiž v [#1] nezmínil, nechápal jsem, proč jej tam máš.
Prkny
Profil
juriad: Něco mám špatně s podmínkou?
INSERT INTO test_volba (id_zbozi, velikost, aktivni)
SELECT ti.id_zbozi, ti.volba, '1' FROM test_import ti
ON DUPLICATE KEY UPDATE aktivni = '1' 
WHERE ti.id_zbozi='222'

MySQL hlásí: Dokumentace
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ti.id_zbozi='222'' at line 4
juriad
Profil
Aha, tak si na konec druhého řádku přidej WHERE podmínku.
juriad
Profil
INSERT může brát data buď z klauzule VALUES, ve které je zadáš ty, nebo ze SELECTu. A úplně na konec může mít INSERT klauzuli ON DUPLICATE KEY UPDATE, která říká, co se má stát, když nastane kolize.

dev.mysql.com/doc/refman/5.6/en/insert.html - ty jsi zvyklý na tu první formu s VALUES. To co jsem radil v [#10] je ta třetí možnost.
Prkny
Profil
juriad:
Promiň, špatně jsem si přečetl tvůj 12 příspěvek.

Už to začínám chápat, již to načítá správná data, ale bohužel tam moc nefunguje tak klauzule ON DUPLICATE KEY UPDATE, jelikož pokud mám toto:
INSERT INTO test_volba (id_zbozi, velikost, aktivni)
SELECT ti.id_zbozi, ti.volba,'1' FROM test_import ti WHERE ti.id_zbozi='222'
ON DUPLICATE KEY UPDATE aktivni = '1'

tak se stane to, že insertuje vše co vybere podmínka (WHERE ti.id_zbozi='222') bez ohledu na to, zda ty data v tabulce test_volba jsou nebo nejsou.

Příklad
tabulka test_insert
|1|222|36
|2|222|38
|3|222|40
|4|445|červená

tabulka test_volba
|1|222|43|0
|2|222|42|0
|3|222|41|0
|4|222|40|1
|5|222|38|0
|6|223|40|1

po výše uvedeném příkazu vypadá je tabulka test_volba takto:
|1|222|43|0
|2|222|42|0
|3|222|41|0
|4|222|40|1
|5|222|38|0
|6|223|40|1
|19|222|40|1
|18|222|38|1
|17|222|36|1


Výsledek by měl být ale tento: (úprava velikosti 38 z 0 na 1 a přidání velikosti 36 s 1)
|1|222|43|0
|2|222|42|0
|3|222|41|0
|4|222|40|1
|5|222|38|1
|6|223|40|1
|17|222|36|1
juriad
Profil
Musíš si vytvořit unikátní index nad vhodnou kombinací sloupcu, nejspíš to bude: id_zbozi a velikost.
Pak ten INSERT bude fungovat přesně jak má - vloží záznamy, které tam nejsou (to zkontroluje ten unikátní index) a ty, které už jsou upraví.
Prkny
Profil
Vytvořit unikátní sloupec by asi šlo pro tabulku test_import a test volba, ale bylo by to velice náročné, jelikož data pro test_import jsou externí data. Tudíž pokaždé před vstupem do mojí tabulky by se muselo data upravit.
Bohužel problém je s fungující živou tabulku Sklad, viz první post , tam je to nemožné vložit automaticky.

Neexistovalo by tedy jiné řešení tohoto problému, rozložené do několika příkazů?
Co třeba upravovat pouze jedno konkrétní id_zbozi:
1) update test_volba set aktivní=0 where id_zbozi='222'
2) vyber všechno z test_import kde id_zbozi = '222' a když test_import.volba = test_import.velikost -> tak aktivni=1, -> else INSERT do test_volba (id_zbozi, volba, aktivni)

Nešlo by toto převést do mysql příkazů i s těmi podmínkami ?
juriad
Profil
# vše změň na 0
UPDATE test_volba SET aktivni = 0;

# vše, co existuje v importu, změň na 1
UPDATE test_volba tv
JOIN test_import ti ON tv.id_zbozi = ti.id_zbozi AND tv.velikost = ti.volba
SET tv.aktivni = 1;

# přidej vše, co neexistuje ve volbě
INSERT INTO test_volba (id_zbozi, velikost, aktivni)
SELECT ti.zbozi_id, ti_volba, '1'
FROM test_import ti
LEFT JOIN test_volba tv ON ti.id_zbozi = tv.id_zbozi AND ti.volba = tv.velikost
WHERE tv.id_zbozi IS NULL;

# poslední dva dotazy podobně pro sklad

Ten unikátní indexy by stačil jen tabulce test_volba a byl by přes dva zmíněné sloupce.
Prkny
Profil
juriad:
Děkuji za pomoc a tvojí nezměrnou trpělivost. Jak jsem psal na začátku moc mysql neumím (ale to bylo asi vidět).
Zaindexování přes dva sloupce unikátním indexem jsem pochopil až nyní, provedl jsem ho a původní příkaz z 16 postu funguje bezchybně.

Ještě jednou děkuji za lekci v MySQL.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: