How to update a table on the basis of difference of current time and column time

I have a table called node_status which has three columns:

1. id (int)
2. status (bit)
3. modifiedtime (smalldatetime)

I want to run a query on it such that if any row in the table contains a modifiedtime value which is more than 5 minutes before开发者_Python百科 the current time then the status column set to 0 (false).

Can any body tell me how to do this? (using SQL Server)

Thanks

The query

UPDATE node_status
SET [Status] = 0
WHERE DATEDIFF(n, ModifiedTime, CURRENT_TIMETAMP) >= 5

will do the job, however, if your node_status table is large, then the performance will be lousy due to table / index scans. If you have an index on ModifiedTime:

DECLARE @ThresholdTime DATETIME
SET @ThresholdTime = DATEADD(n, -5, CURRENT_TIMESTAMP)

UPDATE node_status
SET [Status] = 0
WHERE ModifiedTime <= @ThresholdTime

or even better, if the majority of records have node_status = 0 (i.e. selectivity)

UPDATE node_status
SET [Status] = 0
WHERE ModifiedTime <= @ThresholdTime
    AND [Status] = 1 -- You've stated that status is a bit - I've assumed not nullable.

When you want to do a query based on the current time you can use getdate(), and use the dateadd function to add or remove amounts of time from a date. As such:

update 
     node_status 
set 
     status = 0 
where 
     modifiedtime < dateadd(minute, -5, getdate())

You can use DATEADD function to compute the date/time before five minutes and then compare the modifiedtime column to this value:

UPDATE node_status SET status = 0 WHERE modifiedtime <= dateadd(minute, -5, CURRENT_TIMESTAMP)