Update a list row by matching the value of the a column using REST API

Question

How to update a list row by matching one of the columns of the row using REST API.

Let's say I have a list with column1, column2, and column3. Now I want to search "value5" in "column1" and if "value5" found in a column1 update this row.

I have got this REST API to directly update any specific item by passing the item id:

/_api/web/lists/getbytitle('${listname}')/items(itemId);

But this is not exactly what I am looking for. Can someone help me out with this? Thanks in advance!

Answer 1

There is no direct way to update all the items in one go using REST api. You can consider following steps in order to achieve what your requirements.

1. Get all the items where column match value using REST query i.e. [url to site coll]/_api/web/lists/getByTitle('Title of list')/items?$filter=Column_Internal_Name eq 'Value of column'. Note, in case its a number field dont use ' symbol in value.
2. Use JQuery/JS to loop all the items.
3. Inside loop, create a new item based on the item you received, update the column value to the desired value.
4. Now to the newly item created in step 3, attach another property i.e. metadata property. Its look like __metadata: {type: "SP.Data.ListInternalNameListItem"}. You need to replace ListInternalName with the internal name of list and keep item ListItem at the end of it.
5. Prepare an AJAX request to post this item. The end point will be as in step 1, only you need to remove complete query string.

That's it, your task is done. Now wait for all queries to come with response.

Tip: If you are using JQuery, you can use its $.ajaxStart and $.ajaxStop to track concurrent AJAX requests.

Answer 2

If you want an exact match, use the eq operator or if you want use contains use the substringof operator.

So, first make a REST API GET call to either of the below url:

/_api/web/lists/getbytitle('${listname}')/items?$select=ID&$filter=column1 eq 'value5'

or

/_api/web/lists/getbytitle('${listname}')/items?$select=ID&$filter=substringof('value5,column1)

This will fetch you the list items(s) matching your condition. In the JSON response, you will also get the list item's ID. Now, pass this item id in your mentioned REST API call to update the list item

/_api/web/lists/getbytitle('${listname}')/items("<value of item id received in previous call>")

Answer 3

You can not do it at single request. You need at least two requests.

Find Item

You need to use $filter to find your desired item.

/_api/web/lists/getbytitle('${listname}')/items?$filter=column1 eq 'value5'

Make a GET request to the aforementioned endpoint. It will return your desired item if it exists.

Update Item

Now you know the item id from the above request. Thus, you can update it using the following endpoint.

/_api/web/lists/getbytitle('${listname}')/items(itemId)

NB: Spend some time to study the basic of REST API and SharePoint.