Ryan Shocker Ryan Shocker - 1 month ago 15
SQL Question

Updating int column of all rows that satisfy datediff constraint?

I have a function running every 30 seconds and I want to update a column in all the rows where the

current time - table.lastStoredTime > 5 minutes
.

The
lastStoredTime
is a datetime column and I can't seem to get anything to work well.

Attempt (Doesn't update rows)



SELECT
DATEDIFF(MINUTE, t.lastStoredTime, CURRENT_TIMESTAMP) AS Mtime
FROM Transaction_tbl t
WHERE
AND DATEDIFF(MINUTE,t.lastStoredTime, CURRENT_TIMESTAMP) > 5

Answer Source

test setup:

create table Transaction_tbl (id int identity(1,1) not null, lastStoredTime datetime2(7), intcol int)
insert into Transaction_tbl 
          select dateadd(minute,-7,sysdatetime()), 0
union all select dateadd(minute,-6,sysdatetime()), 0
union all select dateadd(minute,-5,sysdatetime()), 0
union all select dateadd(minute,-4,sysdatetime()), 0
union all select dateadd(minute,-3,sysdatetime()), 0

queries:

/* select */
select *,
  datediff(minute, t.lastStoredTime, sysdatetime()) as Mtime 
from Transaction_tbl t 
where t.lastStoredTime < dateadd(minute,-5,sysdatetime())

/* as an update */
update t
  set intcol = intcol+1
output inserted.*
from Transaction_tbl t 
where t.lastStoredTime < dateadd(minute,-5,sysdatetime())

rextester demo: http://rextester.com/TXYXT83237

select output:

+----+---------------------+--------+-------+
| id |   lastStoredTime    | intcol | Mtime |
+----+---------------------+--------+-------+
|  1 | 2017-08-22 19:31:52 |      0 |     7 |
|  2 | 2017-08-22 19:32:52 |      0 |     6 |
|  3 | 2017-08-22 19:33:52 |      0 |     5 |
+----+---------------------+--------+-------+

update output:

+----+---------------------+--------+
| id |   lastStoredTime    | intcol |
+----+---------------------+--------+
|  1 | 2017-08-22 19:31:52 |      1 |
|  2 | 2017-08-22 19:32:52 |      1 |
|  3 | 2017-08-22 19:33:52 |      1 |
+----+---------------------+--------+

Update using a case expression:

update t
  set intcol = case when t.lastStoredTime < dateadd(minute,-5,sysdatetime()) 
      then intcol+1
      else intcol-1
      end
output inserted.*
from Transaction_tbl t 

output:

+----+---------------------+--------+
| id |   lastStoredTime    | intcol |
+----+---------------------+--------+
|  1 | 2017-08-22 19:47:14 |      2 |
|  2 | 2017-08-22 19:48:14 |      2 |
|  3 | 2017-08-22 19:49:14 |     -1 |
|  4 | 2017-08-22 19:50:14 |     -1 |
|  5 | 2017-08-22 19:51:14 |     -1 |
+----+---------------------+--------+