Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

How can I go about applying an update statement to a on MOSS2007 deployment?

Essentially I'd like to write something similar to the following across it:

update     <list_name>
set        <list_column_x> = z
where      <list_column_y> = p
share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

Updates or other operations that directly target the database are a taboo in SharePoint, so you need to search for another alternative. The batch update infrastructure comes to mind.

Using batch update you can define a set of update commands that will be executed as a batch by the server. That way you will be able to process all your update as a block without the need to programmatically cycle all the list item (read: performance will be better in most case). Notice that this also bear a price: first, the batch IS NOT ATOMIC - if an error arise , there will be no sort of rollback; second, batch updates can get out off hands when it comes to error handling.

The idea behind the batch update process is simple: you need to build a set of "method/commands" declaration according to the batch operation caml schema that define the operation you need to do. For example (taken from microsoft)

<Method ID='1' Cmd='Update'>
   <Field Name='ID'>1</Field>
   <Field Name='DeliveryStatus'>some new value here</Field>
</Method>

This would update the item with id 1 on the list and set the value of the 'DeliveryStatus' field to some new value. You may be wondering how this could help you... what you want to do is first execute a query on the list you must update (for example, use the SPQuery class), then extract the ids of the update-needed items from the query results and build a set of method "declaration" for each item. This tutorial will provvide you a pretty good start if you decide to give this method a try. Also have a look at this blog post, it shows how the same batch instruction can be used in the standard object model (SPWEb.ProcessBatchData(processtext) method) or with the list web service.

Just remember a thing: the batch commands schema is not very well documented (at last when I needed to use it some time ago) so prepare to have fun if you need to update some "uncommon" fields like lookups, multivalue, people fields and so on... in that case google is your best friend (and if anything else fail, leave a question here on sharepoint overflow).

PS: while we are at it, you may well have a look to this question: How to update multiple ListItems with a “Where Clause” using SPServices?. It doesn't really qualify as a duplicate (your question can allow a wider range of solutions), but can provide an insight to your problem in a more specific situation.

share|improve this answer
    
This gives me a great foundation to work from, thank-you –  ElvisLikeBear Jan 11 '12 at 21:52
    
If you decide to alter SQL directly, Microsoft will no longer provide support for your farm. You may get bitten by a pesky update too. –  Russell Jan 12 '12 at 9:29
add comment

I suggest you writing your code in C# or VB.NET, There's no support for direct DB operations.

You can use the OOTB Lists web service to update the list: Lists.UpdateListItems Method (Lists)(http://msdn.microsoft.com/en-us/library/lists.lists.updatelistitems.aspx)

Or use the SharePoint object model like this:

SPList list = web.Lists["ListName"];
//SPListItem item = list.Items.Add();
//item["PercentComplete"] = .45; // 45%
//item.Update();

SPListItemCollection items = list.GetItems(new SPQuery()
{
    Query = @"<Where>
                <Eq>
                   <FieldRef Name='Title' />
                   <Value Type='Text'>Desigining</Value>
                </Eq>
              </Where>"
});

foreach (SPListItem item in items)
{
    item["PercentComplete"] = .45; // 45%
    item.Update();
}
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.