CODEWITHSUNDEEP
sqlsql-server
sk786
sk786

Reputation: 414

Update datetime column by one minute for few rows having same datetime

I have this table with lots of data:

+----+-------+-------------------------+
| id | user  |        datetime         |
+----+-------+-------------------------+
|  1 | 34534 | 2015-08-12 10:03:22.043 |
|  2 | 32423 | 2015-08-12 03:29:18.097 |
|  3 | 12312 | 2015-08-13 03:24:10.073 |
|  4 | 34232 | 2015-08-13 03:24:10.073 |
|  5 | 32462 | 2015-08-13 03:24:10.073 |
|  6 | 45354 | 2015-08-14 04:12:04.023 |
+----+-------+-------------------------+

I want to create a one minute gap between the datetime of rows which are exactly same. Like in above case, row number 3,4,5. One minute gap in these three datetime.

Upvotes: 1

Views: 1192

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

Try this

select id,
       user,
       [datetime] = case when rn = 1 then [datetime] else dateadd(minute,rn-1,[datetime]) END
from
(
select row_number()over(partition by [datetime] order by id) rn,*
from yourtable
) A

For updating the table use this

with cte As
(
select row_number()over(partition by [datetime] order by id) rn,*
from yourtable
) 
Update CTE set [datetime] =  case when rn = 1 then [datetime] else dateadd(minute,rn-1,[datetime]) END

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146429

While exists (Select * from table a
                join table b
                   on b.Id > a.Id
                      and b.datetime = a.DateTime) 
   Update t set
      datetime = DateAdd(minute, 1, DateTime)
   From table t
   where Exists (Select * from table
                 Where id > t.Id 
                   and DateTime = t.DateTime)

Upvotes: 0

Related Questions