Search code examples
mysqlsqldatabasedrupaldrupal-7

I do not want all the values in my table

I have a table with 2.500.000 rows. All this rows has a value of timestamp. I want to keep 100 rows that has the biggest timestamp.. All the other values i want to be deleted!!

$values_to_delete = db_delete('error_log_jira')
    ->condition('timestamp', 1423731524, '>')
    ->execute();

I have this code and works perfect but i do not know how to crate the thing that i want is someone here that can help me?? I need a real help!!

Solution

  • Let's say your table which has 2.500.000 rows is called TableA. You can do this:

    1. Create a temporary table TableB which has the same columns as TableB

    2. Write a query which extracts 100 rows depending on the timestamp

    3. Insert these 100 rows in TableB

    4. Delete all the rows of TableA

    5. Insert all the rows of TableB in TableA and drop TableB

    Steps 2-4 can be done in sql by a query like this:

    insert into TableB (SELECT * FROM TableA ORDER BY timestamp DESC LIMIT 100)