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

Question

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

What do you want to delete from the table? – AdamMc331 Dec 31 '14 at 16:54 — AdamMc331, Dec 31 '14 at 16:54
I want to delete all rows or empty the table @McAdam331 – Samuel Kwame Antwi Dec 31 '14 at 16:58 — Samuel Kwame Antwi, Dec 31 '14 at 16:58
trucante table `tablename` – Emilio Gort Dec 31 '14 at 17:03 — Emilio Gort, Dec 31 '14 at 17:03

Gordon Linoff · Accepted Answer · 2014-12-31T22:06:54.170

3

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

edited Dec 31 '14 at 22:06

answered Dec 31 '14 at 17:02

Gordon Linoff

1,242,037
58
646
786

If I'm not mistaken, you cannot use any conditions with `truncate table`? So for this particular use case it wouldn't work. – AdamMc331 Dec 31 '14 at 17:05
1

@McAdam331 . . . I was trying to say that. I rephrased the answer. – Gordon Linoff Dec 31 '14 at 17:10
@GordonLinoff it worked like a charm and true i tried the truncate table and it did not work. Thank you guys for the help this was effective and Gordon if you can explain what cross join does that would be great to help someone else who has never heard of it use. – Samuel Kwame Antwi Dec 31 '14 at 17:14
Perhaps it was just me that misunderstood. Thanks for rephrasing though. I learned that from this [related](http://stackoverflow.com/questions/3704834/truncate-with-condition) question. – AdamMc331 Dec 31 '14 at 17:15

score 1 · Answer 2 · answered Dec 31 '14 at 16:59

1

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

answered Dec 31 '14 at 16:59

BWS

3,786
18
25

1

But you cannot use any conditions with `truncate` can you? They only want to clear the table when the total row count is greater than 500. – AdamMc331 Dec 31 '14 at 17:03

axiac · Answer 3 · 2014-12-31T18:07:59.287

0

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.

edited Dec 31 '14 at 18:07

answered Dec 31 '14 at 18:00

axiac

68,258
9
99
134

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

3 Answers3