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
SolutionDelete from emp_leave_details
where emp_id='$id'
and DATE(from_date) BETWEEN DATE('$f_date') AND DATE('$t_date')
ExplanationYours 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
How to Set Datetimepicker to Month and Year Only Format
Casting an Object into the Same Type as an Ienumerable Collection
Moq - Non-Overridable Members May Not Be Used in Setup/Verification Expressions
Add Spaces Before Capital Letters
Choosing the Default Value of an Enum Type Without Having to Change Values
Rendering Views from External Assemblies in Mvc6
How to Use Decimal Type in Mongodb
How to Map to a Dictionary Object from Database Results Using Dapper Dot Net
Programmatically Create and Launch and Rdp Session (Without Gui)
Get All Column Names of a Datatable into String Array Using (Linq/Predicate)
Controlling Datetime Parameter Formatting in Webapi 2
What Is the Purpose of a Question Mark After a Type (For Example: Int Myvariable)
Asp.Net Core, Change Default Redirect for Unauthorized
How to Get the Am/Pm Value from a Datetime
Select Distinct by Two Properties in a List
Ssh.Net Sftp Get a List of Directories and Files Recursively
C# Json.Net How to Ignore a Property in Deserialization But Not in Serialization