delete records

edeltatc2

I have a rudimentary understanding of deleting records. What I am attempting to do is delete records after the first 200 (with a couple of WHERE clauses). Is there a conditional that would make this an easy query. I.E.

DELETE FROM table WHERE cat = "business" AND valid = "1"

but only after the first 200 are skipped

devinemke

when you say "first 200" i assume you are refering to an integer field of some sort?

DELETE FROM table WHERE cat = 'business' AND valid = '1' AND int > 200

edeltatc2

When I say the first 200. I mean the first 200 rows that return true from the where clauses.

devinemke

Originally posted by edeltatc2
When I say the first 200. I mean the first 200 rows that return true from the where clauses.

ok then

DELETE FROM table WHERE cat = 'business' AND valid = '1' LIMIT 200

i strongly suggest you also throw in an ORBER BY clause to which which 200 are in fact deleted

edeltatc2

would'nt that delete the first 200? I want to keep the first 200 that return true and delete the rest that return true.

devinemke

you are right, my bad

$result = mysql_query("SELECT * FROM table WHERE cat = 'business' AND valid = '1' ORDER BY some_field");
while ($row = mysql_fetch_assoc($result)) {$array[] = $row['id'];}
$result = mysql_query('DELETE FROM table WHERE id NOT IN(' . implode(',', $array) . ')');

the above exmaple assumes:
1. you are using mySQL
2. the table is called "table"
3. the primary key field of "table" is called "id"

edeltatc2

If I add a limit to it I think that just might work.

$result = mysql_query("SELECT * FROM table WHERE cat = 'business' AND valid = '1' ORDER BY some_field LIMIT 200");
while ($row = mysql_fetch_assoc($result)) {$array[] = $row['id'];}
$result = mysql_query('DELETE FROM table WHERE id NOT IN(' . implode(',', $array) . ')');