how to mark records that are read in mssql

I have created a system that sends some information by SMS daily to our customers. as the procedure of sending SMS can take a while this is done by multiple processes that each process sends a limited number of SMSs. when a Process reads a number of records of pohne nubers (lets say 1000 of them) to send sms. I want to mark these 1000 records that the other process wouldn't read these records too.

Select Top(1000) ID, Number from Phones where isactive = 1 and isverified = 1

I need some code that does something like this:

update Phones, set ReadTime = getdate() where (selected above)

now I want to update the column ReadTime of these records to getdate() to make sure that other schduled processes won't read them again (the reason i'm setting a datetime flag instead of a Bit flag is that some SMS sending might fail and this way I can understand which ones are read long ago but not updated the sent time)

marking them after sending the sms (that should be done anyway to write sent time) wouldn't help as the the scheduled taks that will process the sending SMSs will read all 1000 records at once and there might be records that are read by other processes but not flaged yet (as the SMS hasn't been sent yet).

Answers


You should be able to do something like this:

UPDATE Phones
SET ReadTime = GetDate()
WHERE ID IN (SELECT Top 1000 ID
             FROM Phones
             WHERE IsActive = 1
                 AND IsVerified = 1
                 AND ReadTime Is Null -- you will want to exclude any records already updated
             ORDER BY ID) 

see SQL Fiddle with Demo


Need Your Help

Coding Graphics and KeyListener in java

java swing jframe paint keylistener

I was wondering how to properly use the Graphics library and also the Keylistener in JAVA. Underneath is my code, i believe i have done something wrong because the Window is blank with no Oval. Ple...

Android, front and back camera Orientation , Landscape

java android camera landscape

In my camera app, you can switch between the front and back camera.