[es] - Brisanje recorda iz jedne tabele na bazi druge tabele

igor.vitorac

Član broj: 144858
Poruke: 483

+13 Profil

Brisanje recorda iz jedne tabele na bazi druge tabele

^{14.04.2014. u 11:08 - pre 122 meseci}

Pokusavam da resim problem brisanje rekorda iz jedne tabele na bazi druge (bez koriscenja kursora), pa bi mi svaki savet dobro dosao.

Evo konkretnog problema:
- u jednoj tabeli (Hierarchy) se nalaze razne kombinacije nodova jedne hierarhije pri cemu From/To kombinacije mogu imati duplikata:
ID, From, To
idh_1, A1, C1
idh_2, A1, C1
idh_3, A1, C1
idh_4, A1, C1
idh_5, B1, C1
idh_6, B1, C1
idh_7, B2, C1

- u drugoj temorary (TTable) tabeli se nalaze kombinacije (mogu imati From/To duplikata) koje treba ukloniti iz Hierarchy tabele:
ID, From, To
idt_1, A1, C1
idt_2, A1, C1
idt_3, B1, C1
idt_4, B2, C1

- neophodno je ukloniti onoliko parova From/To iz Hierarchy tabele, koliko se nalazi u temporary TTemp tabeli.
- ogranicenje je da NEPOSTOJI veza tipa foregn key ili da imaju isti primary key.

Ono sto sam trenutno nasao kao moguce resenje je da uvedem kursor za tabelu TTable i za svaki par From/To iz TTable izvrsim:
DELETE FROM Hierarchy WHERE Hierarchy.From = TTable.From AND Hierarchy.To = TTable.To LIMIT 1

Da li neko ima savet kako bi ovaj problem mogao da resim sa nekim Join/Limit/variable/$#%*& ?

Unapred hvala,
Igor

Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3453

Jabber: djoka_l

+1462 Profil

Re: Brisanje recorda iz jedne tabele na bazi druge tabele

^{14.04.2014. u 11:44 - pre 122 meseci}

Kada sam poslednji put gledao MySQL help, nije postojala rank funkcija, pa onda da napišemo sopstvenu:

Code (sql):

SELECT t1.id, t1.fromid, t1.toid, COUNT(*) myrank

  FROM hierarchy t1, hierarchy t2

 WHERE t1.id >= t2.id

   AND t1.fromid = t2.fromid

   AND t1.toid = t2.toid

 GROUP BY t1.id, t1.fromid, t1.toid

Što se tiče TTable, potreban je samo broj ponavljanja (from, to) parova:

Code (sql):

SELECT fromid, toid, COUNT(*) cnt

FROM TTable

GROUP BY fromid, toid

Potrebno je izdvojiti one id iz prve tabele čiji je rank <= broju ponavljanja (from, to) iz TTable:

Code (sql):

SELECT x.id

FROM (SELECT t1.id, t1.fromid, t1.toid, COUNT(*) myrank

  FROM hierarchy t1, hierarchy t2

 WHERE t1.id >= t2.id

   AND t1.fromid = t2.fromid

   AND t1.toid = t2.toid

 GROUP BY t1.id, t1.fromid, t1.toid) x, (

SELECT fromid, toid, COUNT(*) cnt

FROM TTable

GROUP BY fromid, toid) y

WHERE x.fromid = y.fromid

AND x.toid = y.toid

AND x.myrank <= y.cnt

Sada je ostalo samo da napravimo i delete:

Code (sql):

DELETE FROM hierarchy WHERE id IN (

SELECT id

FROM (SELECT x.t1.id, t1.fromid, t1.toid, COUNT(*) myrank

  FROM hierarchy t1, hierarchy t2

 WHERE t1.id >= t2.id

   AND t1.fromid = t2.fromid

   AND t1.toid = t2.toid

 GROUP BY t1.id, t1.fromid, t1.toid) x, (

SELECT fromid, toid, COUNT(*) cnt

FROM TTable

GROUP BY fromid, toid) y

WHERE x.fromid = y.fromid

AND x.toid = y.toid

AND x.myrank <= y.cnt)

Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs

+2377 Profil

Re: Brisanje recorda iz jedne tabele na bazi druge tabele

^{14.04.2014. u 12:17 - pre 122 meseci}

@djoka_l, odlicno i postupno no delete where id in (select..) je ok ako onaj select unutra ima par rezultata, za malo vise je mysql prilicno spor... dakle sql je dobar samo je mysql glup da ga izvrsi brzo pa takav upit moze da traje i danima (zavisno od podataka i od verzije mysql-a, neke su gluplje od nekih drugih)

tako da umesto tog in bolje nesto tipa

Code (sql):

DELETE hierarchy.* FROM hierarchy JOIN 

(SELECT x.id

FROM (SELECT t1.id, t1.fromid, t1.toid, COUNT(*) myrank

  FROM hierarchy t1, hierarchy t2

 WHERE t1.id >= t2.id

   AND t1.fromid = t2.fromid

   AND t1.toid = t2.toid

 GROUP BY t1.id, t1.fromid, t1.toid) x, (

SELECT fromid, toid, COUNT(*) cnt

FROM TTable

GROUP BY fromid, toid) y

WHERE x.fromid = y.fromid

AND x.toid = y.toid

AND x.myrank <= y.cnt) ZZ 

USING (id);

dakle samo umesto IN stavis join .. e sad, deluje mi da i ovo unutra moze da se optimizuje ali sam malo u frci sa vremenom, voleo bih da vidim explain extended + visual explain iz workbench-a ovoga nad realnim podacima

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3453

Jabber: djoka_l

+1462 Profil

Re: Brisanje recorda iz jedne tabele na bazi druge tabele

^{14.04.2014. u 12:24 - pre 122 meseci}

Slažem se s tobom Bogdane, ovaj sam upit pisao na Oracle bazi, a nisam bio siguran kako bih bez IN napisao na MySQL.
Kao što si i rekao, bez test podataka i create table komandi sve je samo nagađanje, samo sam za prvi podupit napravio tabelu i testirao upit, a sve ostalo sam pisao iz glave.
Recimo, pretpostavio sam da je ID unique, inače upit ne valja, ali sam na brzinu improvizovao...

Inače IN ne radi ni na jednoj bazi naročito brzo, ne treba kriviti MySQL.

Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.com
Via: [es] mailing liste

Sajt: mysql.rs

+2377 Profil

Re: Brisanje recorda iz jedne tabele na bazi druge tabele

^{14.04.2014. u 12:29 - pre 122 meseci}

:D vidi se da upit dolazi sa neke od "masivnih" baza ... oracle ovo ume
da sazvace (mislim da bi i pgsql umeo da sazvace to kako treba), na
zalost mysql, jos ako je neki 5.0 ili 5.1 sa ovim ozbiljno razbija
glavu, 5.6 je nesto inteligentniji... e sad, 5.7 bi mozda, zavisno od
toga na sta lice podaci, uspeo da istrci ovo brzo, mada se ne bi kladio
u to :(

e sad, bez obzira koliko rdbms ume kvalitetno da optimizuje exec path za
neki upit, uvek je korisno da ti izoptimizujes upit sa svoje strane
koliko mozes tako da bi i za oracle join bio bolji od in :)

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Odgovor na temu

igor.vitorac

Član broj: 144858
Poruke: 483

+13 Profil

Re: Brisanje recorda iz jedne tabele na bazi druge tabele

^{15.04.2014. u 14:32 - pre 122 meseci}

Hvala momci!
Ne znam kako ovo cudo od SQL radi, ali izgleda da radi. :-)
Testiracu jos malo detaljnije da vidim kako se ponasa.

Pozdrav,
Igor

Odgovor na temu