Whilst creating an application, which works from a central job list, I found myself wanting to perform both a select and update query at the same time. Essentially, marking jobs as collected, at the same time they are collected. This then allows me to run multiple instances of the same app, all picking up their own tasks.
How to update rows in SQL when running a select query
So, how do you update the same row’s that you’re collecting? Well, for my purpose, it was quite straight forward. I just create a date variable, populate it with the current date and time, update the rows with one query, and then use that again with a select. Here’s an example of how I did it:
DECLARE @date AS DATETIME SELECT GETUTCDATE() UPDATE [job_list] SET [collection_date] = @date WHERE id IN ( SELECT TOP (10) id FROM [job_list] WHERE ([collection_date] IS NULL OR DATEADD(DAY,1,[collection_date]) < GETDATE()) AND [processed_date] IS NULL ORDER BY id ); SELECT id FROM [job_list] WHERE [collection_date] = @date ORDER BY id
GetUTCDate() will return a string like this, 2021-05-11 20:11:07.127, which as you can see is down to the millisecond, which is fine for me, but you could also just as easily replace the date field with a client name, or even better, run a combination of them both. GUID’s are also a good option.
You’ll also notice that I used TOP (10) in my sub query, within the where statement of the update query. This is just to limit ho many jobs are collected at a time, the other options in the where statement should be self explaniatory.
I hope this help you.