J0ke Posted June 8, 2006 Report Posted June 8, 2006 subj: mysql 4 selects skaidrs... kvešans tāds: kā šo izmantot delete querijā? vai neķēpāties un izmantot php loopu DELETE FROM mess WHERE receiver IN (subselect); izmet erroru :> SELECT DISTINCT receiver FROM mess WHERE NOT EXISTS (SELECT id FROM usr WHERE usr.id = mess.receiver) LIMIT 50;
Delfins Posted June 8, 2006 Report Posted June 8, 2006 mysql 4 - subselekti ir? Izmanto PHP masīva ģenerēšanai... while ( $row ) { $idsArr[] = $row['field']; } $idsStr = implode( ', ', $idsArr ); $sql = delete from tabula where id in ( $$idsStr )
J0ke Posted June 8, 2006 Author Report Posted June 8, 2006 (edited) CREATE TEMPORARY TABLE tmptable SELECT DISTINCT receiver AS del FROM mess AS t1 LEFT JOIN usr AS t2 ON t2.id = t1.receiver WHERE t2.id IS NULL;# Affected rows: 8309 DELETE FROM mess USING tmptable, mess WHERE mess.receiver = tmptable.del;# Affected rows: 49674 izskatās ka šādi nostrādāja... P.S. vienīg pēc šādas manipulācijas radās 35MB overheads :D OPTIMIZE TABLE `mess` un viss kedā Edited June 8, 2006 by J0ke
Recommended Posts