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
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)
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.
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:
Or use the SharePoint object model like this: