Delete orphaned rows

[J0ke]

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]

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]

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