-2

Hello there i would like to use a DELETE statement in Mysql in order to delete all the entries after the 1000th entry, but on the same time ordering the entries by descending order of a column... This is the code i am using:

    mysql_query("DELETE FROM 'tblname' WHERE @i IN (1000,ROW_COUNT()) ORDER BY points DESC");

Although no error occurred, it doesnt seem to work for some reason.... i am having trouble in the WHERE clause... can anyone help on that ?

Ioannis Petridis
  • 127
  • 1
  • 6

1 Answers1

5

You have quotes around your table name:

mysql_query("DELETE FROM 'tblname' WHERE @i IN (1000,ROW_COUNT()) ORDER BY points DESC");

Replace them with backticks as such:

mysql_query("DELETE FROM `tblname` WHERE @i IN (1000,ROW_COUNT()) ORDER BY points DESC");

Edit: (deleting records)

Try:

mysql_query("DELETE TOP (1000) FROM `tblname` ORDER BY points DESC");

You can also try:

DELETE FROM tablename ORDER BY points DESC LIMIT 1000 *

Assuming this is a column named id:

mysql_query("DELETE FROM tablename WHERE id > 1000");

That would leave the first 1,000 rows and remove every other entry.

A few more examples:

DELETE FROM `table` WHERE `id` < 1000
DELETE FROM `table` WHERE `id` LIMIT 0, 1000
DELETE FROM `table` WHERE ID BETWEEN 1 AND 999

Footnote: If you are using a variable for your table name (which could be a possibility, without seeing full code), use the following, IF this is the case; many do.

$tblname = "your_table_name";
mysql_query("DELETE FROM `".$tblname."` WHERE @i IN (1000,ROW_COUNT()) ORDER BY points DESC");

You should also consider using mysqli_* functions with prepared statements or PDO. The mysql_* functions are deprecated and will be removed from future releases.

Community
  • 1
  • 1
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
  • The quotes are used so i wont refer the actual name of the table... the problem isnt caused there.. it is caused in the WHERE clause... is my code actually looping from 1000 till the end of the entries ? (as mentioned in the WHERE clause --> @i IN (1000,ROW_COUNT())) – Ioannis Petridis Dec 25 '13 at 00:53
  • It would help me as well as others in posting the actual codes and replacing your table name with something else. What errors are you getting, can you provide a `var_dump();` or any error messages? @JohnPetridis – Funk Forty Niner Dec 25 '13 at 00:56
  • See my **edit**, give that a try. I'm not 100% I understand what you mean, but do try it. @JohnPetridis – Funk Forty Niner Dec 25 '13 at 01:14