I want to update all records in my database table with a fixed value for pwDays and an increasing value in pWDate.

My current statement:

update [POSUSER] set passDays = '60', passChangeDate = '2017-01-01 00:00:00'

But I do not want all users to have to reset their passwords on the same day. So I would like to increment the passChangeDate by 1 day for every 10 to 20 users, is there a "simplistic" way to do this in a single SQL statement? (All users must have a new passChangedDate, but no more than 20 users must have the same date.)

The table has a primary key field that is a integer value. (USERID)

Note I want to only increase the date after every +/-10% users.


Since you have a primary key userid, you can do it with a query like :

update p 
set passDays = '60',
    passChangeDate = dateadd(dd, offset, '2017-01-01 00:00:00')
from [POSUSER] p
join (
    select  userid, 
            row_number() over(order by userid) / 10 as offset
    from [POSUSER]
    ) t on p.userid = t.userid

This query will work the same with any type of primary key (numeric or not) or any set of unique columns. But if there is no way to build a unique identifier of a row, the query won't work and you'll have to make your update another way.


I think this is the most simple way to do it.

update [POSUSER] set passDays = '60', passChangeDate = DATEADD(day, (userid % 10) ,'2017-01-01 00:00:00')

So you get 10 different days for all users to reset their password due to the modules 10. If you want to spread it over 20 days, use userid % 20. This assumes of course that the userid is unique, and auto increment.

This query would not set multiple dates if all user ids would increment with the same value as the chosen modules. Because it would result in the same day increment every time.


Ok I played around a bit and this works very well (adapted from Auto Increment in an UPDATE statement by Phil Haack), except for 1 small issue:

DECLARE @passChangeDate smalldatetime
SET @passChangeDate = '2017-01-09 00:00:00'

update [POSUSER] 
set @passChangeDate = passChangeDate = DATEADD(dd,1,@passChangeDate),
passDays = '60';

The above increments every users date with 1 day, but I would prefer only every 10 - 20 users. But maybe the simple answer is to use hours?

DECLARE @passChangeDate smalldatetime
SET @passChangeDate = '2017-01-09 00:00:00'
update [POSUSER] 
set @passChangeDate = passChangeDate = DATEADD(hh,1,@passChangeDate),
passDays = '60';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.