Sql-server – Update multiple rows with different date for each row

sql serversql-server-2012

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.

Best Answer

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.

Related Question