CODEWITHSUNDEEP
phpmysqlcasesql-delete
Samuel Kwame Antwi
Samuel Kwame Antwi

Reputation: 597

Delete all rows from a table when total row count exceeds a number

I am trying to optimize my queries and I have the following code:

<?php
$counter = mysql_query("SELECT COUNT(*) AS c FROM table");
$row = mysql_fetch_array($counter);
$count = $row["c"];
if($count>500){
mysql_query("DELETE FROM table");
}
?>

I tried the below syntax as a test and it worked:

Select case when (Select count(*) as t from `table`) > 500 then 1 else 2 end

But this throws an error:

Select case when (Select count(*) as t from `table`) > 500 then (DELETE FROM table) else null end

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 'DELETE FROM table else null end LIMIT 0, 25' at line 1

I understand there is a syntax error, but my main question is why the case statement works when I place integers in the condition, but the delete statement throws an error. I tried a select statement and it worked fine:

Select case when (Select count(*) as t from `table`) < 500 then (select count(*) from table) else null end

Upvotes: 1

Views: 1370

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

If you really wanted to do this with a single delete:

delete t
     from table t cross join
          (select count(*) as cnt from table t) c
     where c.cnt > 500;

Do note (as BWS does) that truncate table is more efficient, although you cannot use a where clause or join with truncate table

Upvotes: 3

axiac
axiac

Reputation: 72226

The CASE statement works with SELECT when the SELECT statement produces a single value (i.e. it selects only one column and the result set has a single row); it doesn't work otherwise. Obviously, a DELETE (or INSERT, UPDATE etc) never works in that position.

Upvotes: 0

BWS
BWS

Reputation: 3836

If you are trying to delete all the rows from the table, just use "TRUNCATE table"

Upvotes: 1

Related Questions