Delete Multiple List Items in Batches

Apr 4, 2012 at 2:52 PM

Hello,

I am trying to delete multiple list items (order is not important) in batches of preferably a couple thousand at a time using SPServices. I essentially want to have a button that I can click to run the function. Currently, I am not sure if it is my function or the code I have written. Any advice would be greatly appreciated.

Please let me know if you need any additional information.

<script type="text/javascript" src="http://max/sites/kbedell/Scripts/jquery.SPServices-0.7.1a.min.js"></script>
<script type="text/javascript" src="http://max/sites/kbedell/Scripts/jquery-1.7.2.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {

        $("#deleteButton").click(function () {

            var myQueryOptions = "<QueryOptions><ViewAttributes Scope='RecursiveAll' IncludeRootFolder='True' /></QueryOptions>";
            var myQuery = "<Query><Where><IsNotNull><FieldRef Name='LinkTitle' /><IsNotNull></Where></Query>";

            $().SPServices({
                operation: 'GetListItems',
                async: false,
                listName: "TestLibrary",
                CAMLViewFields: '<ViewFields><FieldRef Name="ID" /></ViewFields>',
                CAMLQuery: myQuery,
                CAMLRowLimit: 10,
                CAMLQueryOptions: myQueryOptions,
                completefunc: function (xData, Status) {
                    $(xData.responseXML).find("[nodeName='z:row']").each(function () {
                        var ID = $(this).attr("ows_ID");
                        $().SPServices({
                            operation: "UpdateListItems",
                            listName: "TestLibrary",
                            updates: "<Batch OnError='Continue'>" +
                                       "<Method ID='1' Cmd='Delete'>" +
                                         "<FieldRef Name='ID'>" + ID + "</Field>" +
                                       "</Method>" +
                                     "</Batch>",
                            completefunc: function (xData, Status) {
                                alert(xData.responseXML.xml);
                            }
                        });
                    });
                }
            });
        });
    });
</script>

<button id="deleteButton">Delete List Items</button>

Coordinator
Apr 4, 2012 at 5:07 PM

You should check out this other thread which is active and covering a similar requirement.
http://spservices.codeplex.com/discussions/351060#post819913

You don't specify what the issue is. Are some items deleting? Are you getting an error?

M.

Apr 4, 2012 at 5:34 PM

Hi Marc,

My code doesn't seem to do anything. I've tested the button code with an alert, and the alert will go off. However, if I then replace an alert with what I have above I don't even get an error code... 

I will keep my eye on that thread you referenced, and see if maybe I can incorporate it into what I need.

Thanks.

Coordinator
Apr 5, 2012 at 4:18 PM

Make sure that your call to GetListItems is returning the items you want to delete first. If you want to delete a large number of items, you should probably batch them in fewer requests, too, which would require rethinking your approach a little. Save all the IDs in an array and then build batch requests with n items per request.

M.

May 16, 2012 at 1:03 PM

I figured I'd reply just in case someone else runs into this. I never did end-up getting it to work in batches; however, eventually I was able to delete things using the following. (I probably can modify this to be recursive with a wait, to get the job done.)

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.5/jquery.min.js"></script>
<script src="http://cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/0.7.1a/jquery.SPServices-0.7.1a.min.js"></script>
<script type="text/javascript">
    $(document).ready(function() {
        $("input[class='buttonpush']").click(function() {
		$().SPServices.SPUpdateMultipleListItems({ 
		webURL: "http://sharepoint.partners.org/mgh/anesprofdatacapturemodule/",
		listName: "Site Collection Recycle Bin",  
		CAMLQuery: "<Query><Where><And><Gt><FieldRef Name='ID' /><Value Type='Counter'>1</Value></Gt><Lt><FieldRef Name='ID' /><Value Type='Counter'>2000</Value></Lt></And></Where></Query>",  
		batchCmd: "Delete",  
		valuepairs: [],  
		debug: false,
			completefunc: function (xData, Status) {
                    alert(xData.responseXML.xml);
					}
			});
        });
    });
</script>

<h1>Press to Delete Admin Recycle Bin</h1>

<input type=button class="buttonpush" value="Delete" />

May 16, 2012 at 1:57 PM

In your first example, you're using the syntax .find("[nodeName='z:row']").  If you are using jQuery 1.7.2, that syntax will not work.  Marc made a note about that on the home page of this site.  You need to replace that syntax with .SPFilterNode("z:row").  SPServices 0.7.1a added the SPFilterNode function to deal with the change in the jQuery module.

Coordinator
May 16, 2012 at 11:19 PM

Good catch; I totally missed that. Since the

.find("[nodeName='z:row']")

 syntax no longer works, there would be no items matched by the selector and therefore, nothing would happen.

M.

May 21, 2012 at 7:10 PM

Ah okay. I missed that note as well then. Thanks GKOliver. I may at some point revisit my original script when I have a spare moment.

Jun 7, 2012 at 3:13 PM

I'll share what I do to delete in batches - this script is delete all items in list, and if there more that 100 items do it in batches of 100. The script start at the lowest ID and then deletes in batches to the highest ID, it doesn't look at whether or not there are any items between the min & max, it just executes the delete.

  1. on load display the highest & lowest ID from the list which helps indicate how many batch deletions its going to do (for reference and helps determine ~ how many items are in the list - if you want the exact number you should change this to do a getlistitems & use rowcount)
  2. onclick runs the delete
    • if there are more that 100 it will cycle through in batches of 100 until there is less than 100 left in the list then execute a delete for the last 100
    • if there less then 100 it will delete from lowID to highID

 I will admit that it will freeze IE for several minutes during the execution & the html ul with the batch deletions wont show until completed (I planned on adding a delay between the batches but never got around to it). Also, I did not include any status error checks on the deletion execution as you could just reload the page, and if there are not lowID and highID then everything is deleted.

For testing this, I hard coded the highID & lowID values (which you see remarked below) so I could test the <100 & >100 scenario without actually deleting everything in the list.

$(document).ready(function()
{
 var highID=0;
 var lowID=0;
 
//get max ID
 $().SPServices({
	operation: "GetListItems",
	listName: "{1BE4EF18-CA0E-4495-A443-BCBCEBAD7423}",
	CAMLQuery:"<Query><OrderBy><FieldRef Name='ID' Ascending='False' /></OrderBy></Query>",
	CAMLRowLimit: 1,
	completefunc: function (xData, Status) {
        //var stuff = xData.responseXML.xml;
		//$("#Status").text(stuff);
		$(xData.responseXML).SPFilterNode("z:row").each(function() {
   			var liHtml = "<li>" + "Highest ID: " + $(this).attr("ows_ID") + "</li>";
			$("#maxIDUL").append(liHtml);
			highID=parseInt($(this).attr("ows_ID"));
			}); 

		}   
	}); 
	
//get lowest ID
 $().SPServices({
	operation: "GetListItems",
	listName: "{1BE4EF18-CA0E-4495-A443-BCBCEBAD7423}",
	CAMLQuery:"<Query><OrderBy><FieldRef Name='ID' Ascending='True' /></OrderBy></Query>",
	CAMLRowLimit: 1,
	completefunc: function (xData, Status) {
        //var stuff = xData.responseXML.xml;
		//$("#Status").text(stuff);
		$(xData.responseXML).SPFilterNode("z:row").each(function() {
   			var liHtml = "<li>" + "Lowest ID: " + $(this).attr("ows_ID") + "</li>";
			$("#maxIDUL").append(liHtml);
			lowID=parseInt($(this).attr("ows_ID"));
			}); 

		}   
	}); 

 $("#btnChangeCT").click(function(){

//var lowID = 10000
//var highID = 10002

var liHTML = "<li>" +"Start - Deleting from " + lowID + " - " + highID + "</li>";
$("#maxIDUL").append(liHTML);

if (highID-lowID>100){ //delete in batches of 100 if more than 100
	var i=lowID;
	var j=lowID+100;
	while (j < highID){
		liHTML = "<li>" +"-- Batch - Deleting from " + i + " - " + j + "</li>";
		$("#maxIDUL").append(liHTML);
		$().SPServices.SPUpdateMultipleListItems({
			listName: "{1BE4EF18-CA0E-4495-A443-BCBCEBAD7423}",
			CAMLQuery: "<Query><Where><Leq><FieldRef Name='ID'/><Value Type='Integer'>"+j+"</Value></Leq></Where></Query>",
			batchCmd: "Delete",
			debug: true
			});
		i=i+100;
		j=j+100;
	}
	var lower=highID-100;
	liHTML = "<li>" +"-- Final - Deleting from " + lower + " - " + highID + "</li>";
	$("#maxIDUL").append(liHTML);
	$().SPServices.SPUpdateMultipleListItems({
		listName: "{1BE4EF18-CA0E-4495-A443-BCBCEBAD7423}",
		CAMLQuery: "<Query><Where><Leq><FieldRef Name='ID'/><Value Type='Integer'>"+highID+"</Value></Leq></Where></Query>",
		batchCmd: "Delete",
		debug: true
		});}
else { //delete high to low as it's less than 100
	liHTML = "<li>" +"Single delete - Deleting from " + lowID + " - " + highID + "</li>";
	$("#maxIDUL").append(liHTML);
	$().SPServices.SPUpdateMultipleListItems({
		listName: "{1BE4EF18-CA0E-4495-A443-BCBCEBAD7423}",
		CAMLQuery: "<Query><Where><Leq><FieldRef Name='ID'/><Value Type='Integer'>"+highID+"</Value></Leq></Where></Query>",
		batchCmd: "Delete",
		debug: true
		});	}

liHTML = "<li>" +"Deleting from " + lowID + " - " + highID + "<font size='3' color='red' face='verdana'>-->COMPLETE</font>" + "</li>";
$("#maxIDUL").append(liHTML);

});

});
Jun 7, 2012 at 3:13 PM

I'll share what I do to delete in batches - this script is delete all items in list, and if there more that 100 items do it in batches of 100. The script start at the lowest ID and then deletes in batches to the highest ID, it doesn't look at whether or not there are any items between the min & max, it just executes the delete.

  1. on load display the highest & lowest ID from the list which helps indicate how many batch deletions its going to do (for reference and helps determine ~ how many items are in the list - if you want the exact number you should change this to do a getlistitems & use rowcount)
  2. onclick runs the delete
    • if there are more that 100 it will cycle through in batches of 100 until there is less than 100 left in the list then execute a delete for the last 100
    • if there less then 100 it will delete from lowID to highID

 I will admit that it will freeze IE for several minutes during the execution & the html ul with the batch deletions wont show until completed (I planned on adding a delay between the batches but never got around to it). Also, I did not include any status error checks on the deletion execution as you could just reload the page, and if there are not lowID and highID then everything is deleted.

For testing this, I hard coded the highID & lowID values (which you see remarked below) so I could test the <100 & >100 scenario without actually deleting everything in the list.

$(document).ready(function()
{
 var highID=0;
 var lowID=0;
 
//get max ID
 $().SPServices({
	operation: "GetListItems",
	listName: "{1BE4EF18-CA0E-4495-A443-BCBCEBAD7423}",
	CAMLQuery:"<Query><OrderBy><FieldRef Name='ID' Ascending='False' /></OrderBy></Query>",
	CAMLRowLimit: 1,
	completefunc: function (xData, Status) {
        //var stuff = xData.responseXML.xml;
		//$("#Status").text(stuff);
		$(xData.responseXML).SPFilterNode("z:row").each(function() {
   			var liHtml = "<li>" + "Highest ID: " + $(this).attr("ows_ID") + "</li>";
			$("#maxIDUL").append(liHtml);
			highID=parseInt($(this).attr("ows_ID"));
			}); 

		}   
	}); 
	
//get lowest ID
 $().SPServices({
	operation: "GetListItems",
	listName: "{1BE4EF18-CA0E-4495-A443-BCBCEBAD7423}",
	CAMLQuery:"<Query><OrderBy><FieldRef Name='ID' Ascending='True' /></OrderBy></Query>",
	CAMLRowLimit: 1,
	completefunc: function (xData, Status) {
        //var stuff = xData.responseXML.xml;
		//$("#Status").text(stuff);
		$(xData.responseXML).SPFilterNode("z:row").each(function() {
   			var liHtml = "<li>" + "Lowest ID: " + $(this).attr("ows_ID") + "</li>";
			$("#maxIDUL").append(liHtml);
			lowID=parseInt($(this).attr("ows_ID"));
			}); 

		}   
	}); 

 $("#btnChangeCT").click(function(){

//var lowID = 10000
//var highID = 10002

var liHTML = "<li>" +"Start - Deleting from " + lowID + " - " + highID + "</li>";
$("#maxIDUL").append(liHTML);

if (highID-lowID>100){ //delete in batches of 100 if more than 100
	var i=lowID;
	var j=lowID+100;
	while (j < highID){
		liHTML = "<li>" +"-- Batch - Deleting from " + i + " - " + j + "</li>";
		$("#maxIDUL").append(liHTML);
		$().SPServices.SPUpdateMultipleListItems({
			listName: "{1BE4EF18-CA0E-4495-A443-BCBCEBAD7423}",
			CAMLQuery: "<Query><Where><Leq><FieldRef Name='ID'/><Value Type='Integer'>"+j+"</Value></Leq></Where></Query>",
			batchCmd: "Delete",
			debug: true
			});
		i=i+100;
		j=j+100;
	}
	var lower=highID-100;
	liHTML = "<li>" +"-- Final - Deleting from " + lower + " - " + highID + "</li>";
	$("#maxIDUL").append(liHTML);
	$().SPServices.SPUpdateMultipleListItems({
		listName: "{1BE4EF18-CA0E-4495-A443-BCBCEBAD7423}",
		CAMLQuery: "<Query><Where><Leq><FieldRef Name='ID'/><Value Type='Integer'>"+highID+"</Value></Leq></Where></Query>",
		batchCmd: "Delete",
		debug: true
		});}
else { //delete high to low as it's less than 100
	liHTML = "<li>" +"Single delete - Deleting from " + lowID + " - " + highID + "</li>";
	$("#maxIDUL").append(liHTML);
	$().SPServices.SPUpdateMultipleListItems({
		listName: "{1BE4EF18-CA0E-4495-A443-BCBCEBAD7423}",
		CAMLQuery: "<Query><Where><Leq><FieldRef Name='ID'/><Value Type='Integer'>"+highID+"</Value></Leq></Where></Query>",
		batchCmd: "Delete",
		debug: true
		});	}

liHTML = "<li>" +"Deleting from " + lowID + " - " + highID + "<font size='3' color='red' face='verdana'>-->COMPLETE</font>" + "</li>";
$("#maxIDUL").append(liHTML);

});

});
Jan 2, 2015 at 12:41 PM
Delete single list item in library or list .I used SPUpdateMultipleListItems
Click this link