By Jverg on

I have a table with 2.000.000 rows. These rows have a timestamp column and i want to delete all the values except 100 rows with the biggest timestamp. Any ideas on how to do this thing?? I will be very gratefull for your help..

⋅ Categories: Drupal 7.x

Comments

Ayesh’s picture

Ayesh commented

This is not Drupal specific, but you can simply delete rows with a condition.

db_delete('my_table')->condition('timestamp', 12345678, '>')->execute();

What's new and changing in PHP 8

Jverg’s picture

Jverg commented

i need something like this :

$values_to_delete = db_delete('table1', 't');
  $values_to_delete->orderBy('t.timestamp');
  $values_to_delete->range(101, 99999999);
  $values_to_delete->execute();
Jaypan’s picture

Jaypan commented

Take a backup before you execute this:

db_query('DELETE FROM {table1} WHERE timestamp < (SELECT timestamp FROM {table1} ORDER BY timestamp DESC LIMIT 99, 1)');

Note that this should leave the most recent 100 timestamp in the table, which is what I understood from your original post.

Jverg’s picture

Jverg commented

Yes it is what i really want to achieve but your code do not work for me and i dot know why

Jaypan’s picture

Jaypan commented

Probably because your table isn't named table1.

Jverg’s picture

Jverg commented

i put the name of my table and not table1

Jaypan’s picture

Jaypan commented

Probably something else in your code then.

Jverg’s picture

Jverg commented 
$blabla = ('DELETE FROM errors WHERE timestamp <
             (SELECT timestamp FROM errors 
              ORDER BY timestamp DESC LIMIT 99, 1)');
  $result = db_query($blabla);
Jaypan’s picture

Jaypan commented

Try this:

db_query
(
  'DELETE FROM {errors} WHERE timestamp < (SELECT timestamp FROM {errors} ORDER BY timestamp DESC LIMIT :ninetynine, :one)',
  array(':ninetynine' => 99, ':one' => 1)
);
Jverg’s picture

Jverg commented

:( :( :( :(

Jaypan’s picture

Jaypan commented

How are you executing this code? In what function? Are you sure it's even being executed?

Jverg’s picture

Jverg commented

i have a function with that code and i call this function on cron run and when cron is running i am having also these values.

Jaypan’s picture

Jaypan commented

What does $timestamp equal here:

$timestamp = db_query
(
  'SELECT timestamp FROM {errors} ORDER BY timestamp DESC LIMIT :ninetynine, :one',
  array(':ninetynine' => 99, ':one' => 1)
)->fetchField();
Jverg’s picture

Jverg commented

These lines of code also do not do nothing... Maybe if we use something like "rowCount" ?

Jverg’s picture

Jverg commented

If i use these lines of code it works perfect and deletes all the values that have timestamp > 1423662475

$values_to_delete = db_delete('errors')
    ->condition('timestamp', 1423662475, '>')
    ->execute();