How to Delete Records Between Two Dates in Bulk

How to delete records between two dates in bulk?

Now that we've clarified the requirement, here's the gist of how I would solve this problem. It uses a simplified FetchXML query, but you'll get the idea.

Please note the addition of top='2000' to the Fetch.

var fetchXml = @" <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' top='2000'>
                     <entity name='new_sampledata' />                                  
                   </fetch>";

var result = svc.RetrieveMultiple(new FetchExpression(fetchXml));

var entityRefs = result.Entities.Select(e=> e.ToEntityReference());

//instead of e.ToEntityReference(), you'd use e.GetAttributeValue<EntityReference>("new_alterunitorderid")
//like this:
//var entityRefs = result.Entities.Select(e=> e.GetAttributeValue<EntityReference>("new_alterunitorderid"));

var batchSize = 1000;
var batchNum = 0;
var numDeleted = 0;

while (numDeleted < entityRefs.Count())
{
    var multiReq = new ExecuteMultipleRequest()
    {
        Settings = new ExecuteMultipleSettings()
        {
            ContinueOnError = false,
            ReturnResponses = false
        },
        Requests = new OrganizationRequestCollection()
    };

    var currentList = entityRefs.Skip(batchSize * batchNum).Take(batchSize).ToList();

    currentList.ForEach(r => multiReq.Requests.Add(new DeleteRequest { Target = r }));

    svc.Execute(multiReq);

    numDeleted += currentList.Count;
    batchNum++;
}

delete millions for records from table between date range

There are two basic approaches to do massive DELETE operations.

1) Create another table, drop old one and rename the new one, and ANALYZE the new table in the end:

begin;
create table camera_activities_new (like camera_activities including all);

insert into camera_activities_new
select * from camera_activities
where done_at >= ''2016-01-01'::date;

alter sequence camera_activities_id_seq owned by camera_activities_new;
drop table camera_activities;
alter table camera_activities_new rename to camera_activities;
alter index camera_activities_new_camera_id_done_at_idx rename to camera_activities_camera_id_done_at_idx;
commit;

analyze camera_activities;

This approach guarantees that the resulting table will be in the best shape (no bloating). But it can be less convenient your system is heavy loaded and the table is involved. In such cases, "smooth deletion" might look better.

2) "Smooth" deletion: delete only relatively small amount of rows each time, use more aggressive autovacuum settings and control bloating.

Example, showing how to split deletion to many independent transactions (in bash; relies on $PGDATABASE, $PGHOST, $PGUSER, $PGPASSWORD environment variables):

while true; do
  res=$(psql -c "delete from camera_activities where id in (select id camera_activities where done_at < '2016-01-01'::date limit 500);" \
    | grep DELETE | awk {'print $2'} )
  if [[ $res = '0' ]]; then break; fi;
  sleep 0.3; # control speed here; check bloating level
done

– this will stop automatically when no rows are left to delete.

Your index on (camera_id, done_at) should speed up subselect, making Bitmap Index Scan – check with EXPLAIN. But probably it's worth to have a separate index on done_at, it can be btree or brin (lossy but smaller in size) in this case:

create i_camera_activities_done_at on camera_activities using brin(done_at);

Example of "more aggressive" (than default) autovacuum settings:

log_autovacuum_min_duration = 0
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.05
autovacuum_naptime = 60
autovacuum_vacuum_cost_delay = 20

Different queries which help to see your table's bloating level:

• https://wiki.postgresql.org/wiki/Show_database_bloat
• http://blog.ioguix.net/postgresql/2014/09/10/Bloat-estimation-for-tables.html
• https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat-82-84.sql
• https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat.sql (and for indexes:
• https://github.com/dataegret/pg-utils/blob/master/sql/index_bloat.sql; these queries require pgstattuple extension)

SQL Server - Deleting rows between a date range using SQL. Date conversion fails

You wrote 31st of February... Maybe..... that date doesn't exists.

DELETE FROM BIZ 
WHERE [Orgnl_Cmpltn_Date]
BETWEEN '2014-02-28'  AND '2014-04-01'

For a general idea of convert date:

DELETE FROM BIZ 
WHERE [Orgnl_Cmpltn_Date]
BETWEEN CONVERT(date,'2014.02.28',102) and CONVERT(date,'2014.04.01',102)

Here you can find the complete list of values for third parameter of CONVERT
https://msdn.microsoft.com/en-us/library/ms187928.aspx

Delete rows older than 14 days in MySQL

I understand your point about using safe mode. If you try to use UPDATE or DELETE against a non-indexed expression, it complains, because it can't make an estimate of whether you will accidentally delete your whole table.

Using an expression on DATE(dateHeureModification) > ...
is naturally unindexed. MySQL can't do an index lookup against the result of a function.

You can use LIMIT in your delete query to make it satisfy the safe-updates mode. MySQL treats it as sufficient protection against accidentally deleting all the rows in the table, if you use LIMIT.

DELETE
    FROM SYS_VERROUS_POLICE
    WHERE DATE(dateHeureModification) < (curdate() - INTERVAL 14 DAY)
    LIMIT 1000;

It's a good idea to run the delete in limited-size batches anyway, so it doesn't create too many locks or add too much to the undo segment.

Just keep doing DELETE in a loop, deleting batches of 1000 rows at a time, and check rows-affected after each batch. Stop the loop when rows-affected reaches 0.

Another idea: I don't think you really need the DATE() function in your WHERE clause. So you might be able to do the DELETE like below, and it will be able to use an index. Besides, it should be faster to the query to check for any rows if you have an index on dateHeureModification.

DELETE
    FROM SYS_VERROUS_POLICE
    WHERE dateHeureModification < (curdate() - INTERVAL 14 DAY)
    LIMIT 1000;

Mysql Delete between two dates

Solution

Delete from emp_leave_details 
where emp_id='$id' 
and DATE(from_date) BETWEEN DATE('$f_date') AND DATE('$t_date')

Explanation

Yours query is equal to from_date >= '$f_date' AND from_date <= '$t_date', meaning it will ignore all that last day ('2013-09-05' < '2013-09-05 00:00:01'). Mine above gives ('2013-09-05' <= '2013-09-05') instead.

DATE() simply strips the values of hours, minutes and seconds and gives you expected result since BETWEEN uses <= and >=.

deleting records from sqlite on the basis of date in android

If you absolutely want to keep the date as a string in the form MM-DD-YYYY in your database column, then the only way to do comparison of those dates is to convert them to seconds using SQLite's strftime function. However, in order to do that, you have to restructure the date as YYYY-MM-DD because your current format is not one that can be used as input to the date and time functions of SQLite.

Here is a sample:

DateFormat dateFormat = new SimpleDateFormat("MM-dd-yyyy");
Calendar calObj = Calendar.getInstance();
String currentDate = dateFormat.format(calObj.getTime());

String where = KEY_ATTENDANCE_SYN_STATUS + " = ?1 AND "
        + "STRFTIME('%s', SUBSTR(" + KEY_ATTENDANCE_DATE_ONLY + ",7) "
        + "|| '-' || SUBSTR(" + KEY_ATTENDANCE_DATE_ONLY + ",1,5)) < "
        + "STRFTIME('%s', SUBSTR(?2,7) || '-' || SUBSTR(?2,1,5))";
String[] whereArgs = {"C", currentDate};
int rows = db.delete(DATABASE_TABLE_DAILY_ATTENDANCE, where, whereArgs);

If you use yyyy-MM-dd when creating currentDate, you can replace the second instance of the ugly substring + concatenation with just STRFTIME('%s', ?2), but you will still need the first substring + concatenation to transform the column values in the table.

If it's not too late for you to change how your database stores the date, make your life easier by either storing as yyyy-MM-dd (to get rid of all the substring + concatenation above), or better yet store the long date and only worry about converting it to and from MM-dd-yyyy at the java layer.

PHP Delete MySQL Records Older Than 3 Days

Try to provide date by calculating first and then use it in query like below

$date = date("m-d-Y", strtotime('-3 day'));
$conn = getConnected("oversizeBoard");
mysqli_query($conn, "DELETE FROM postedLoads WHERE date < '".$date."');

It might help you. If need any other solution or help, do ask here.

Delete records from more than 1 year ago

For this you need 2 steps. First of all you need a method that will take as a parameter the date of which you want to delete the messages and you dont need tha @Query annotation at all.

So in your repository you must have something like

    @Modifying
    public void deleteByCreatedAtBefore(Date expiryDate);

Now in your service method, you will calculate the Date and pass it on like this

    public void performTweetCleanup(){
       //calculate date
       Calendar cal = Calendar.getInstance();
       Date today = cal.getTime();
       cal.add(Calendar.YEAR, -1);
       Date previousYear = cal.getTime();

       //call the method
       MyTweeterRepository.deleteByCreatedAtBefore(previousYear);
     }

How to delete multiple records using Laravel Eloquent

The issue is that you're calling delete() on a Collection, which does not have that method.

You have a couple options here.

Model Events

If you have event listeners for the deleting/deleted model events, you will need to make sure the deletion happens in a way that each model is loaded and then deleted.

In this case, you can use the destroy method on the model that takes a list of ids. It will load a new model for each id, and then call delete() on it. As you mention in a comment, it won't restrict the deletion to only those products in the organization, so you would need to filter out those ids before passing the list into the destroy() method.

public function destroy($id)
{
    try {
        $ids = explode(",", $id);
        // intersect the product ids for the org with those passed in
        $orgIds = array_intersect($org->products()->lists('id'), $ids);
        // now this will only destroy ids associated with the org
        \App\Product::destroy($orgIds);
    }
    catch(...) {
    }
}

If you don't particularly like that approach, you will need to iterate your collection of organization products and call delete() on them individually. You can use a standard foreach, or you can use the each method on the collection:

public function destroy($id)
{
    try {
        $ids = explode(",", $id);
        $org->products()->find($ids)->each(function ($product, $key) {
            $product->delete();
        });
    }
    catch(...) {
    }
}

No Model Events

Now, if you don't have any model events that you need to listen for, things are a little easier. In this case, you can just call delete() on the query builder, and it will go straight to deleting the records without loading any model objects. So, you get cleaner code with better performance:

public function destroy($id)
{
    try {
        $ids = explode(",", $id);
        // call delete on the query builder (no get())
        $org->products()->whereIn('id', $ids)->delete();
    }
    catch(...) {
    }
}

---

Related Topics