Need to amend dupilcate entries is a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need to amend dupilcate entries is a table

Author

Topic

davematlynch
Starting Member

2 Posts

Posted - 2012-03-01 : 15:27:46

Hi,

I have two colums NamePart and RemovedDate in a table dbo.Constants (5000 + records with 970 dupilcate records).

NamePart is a nvarchar and RemovedDate is a datetime

I want to amend the Visited_delete by a .001 of milisecond where a record has dupilcate entries.

NamePart RemovedDate
user1 2011-08-15 17:11:05.367
user1 2011-08-15 17:11:05.367

SELECT distinct N1.NamePart,N1.NamePart,N1.RemovedDate,N2.RemovedDate
FROM dbo.Constants N1
INNER JOIN dbo.Constants N2 ON ((N1.NamePart = N2.NamePart) AND (N1.RemovedDate = N2.RemovedDate))

How would i go about adding .001 to the end of N2.RemovedDate?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-01 : 15:53:58

Assuming you are using DATETIME2 datatype with a precision of 3 or higher, you could use the DATEADD function:

DECLARE @StartDate DATETIME2(3) = CURRENT_TIMESTAMP

SELECT @StartDate,DATEADD(MILLISECOND, 1, @StartDate)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-03-01 : 15:58:31

Update a
set removedate = dateadd(millisecond, a.row_id - 1, Removeddate)
from
(
select Row_number() over (partition by namepart,Removeddate order by namepart) as row_ID,Namepart,RemovedDate
from mytable
) a

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-03-01 : 16:16:09

Actually, I tested it out and it works perfectly fine with seconds, but millisecond produces very strange results


drop table #TMP
declare @date datetime
set @date = getdate()
select * 
into #TMP
from
(select 1 as namepart,convert(datetime,@date) as Removeddate union all
select 1,@date union all
select 1,@date union all
select 1,@date union all
select 1,@date union all
select 1,@date union all
select 1,@date
) a

select * from #TMP

Update a
set a.removeddate = dateadd(millisecond, a.row_id - 1, Removeddate)
from
(
select Row_number() over (partition by namepart,Removeddate order by namepart) as row_ID,Namepart,RemovedDate
from #tmp
) a

select * from #tmp
/*RESULTS
namepart	Removeddate
1	2012-03-01 15:08:21.060
1	2012-03-01 15:08:21.060
1	2012-03-01 15:08:21.063
1	2012-03-01 15:08:21.063
1	2012-03-01 15:08:21.063
1	2012-03-01 15:08:21.067
1	2012-03-01 15:08:21.067
*/

But with seconds, no problem, any ideas?


drop table #TMP
declare @date datetime
set @date = getdate()
select * 
into #TMP
from
(select 1 as namepart,convert(datetime,@date) as Removeddate union all
select 1,@date union all
select 1,@date union all
select 1,@date union all
select 1,@date union all
select 1,@date union all
select 1,@date
) a

select * from #TMP

Update a
set a.removeddate = dateadd(second, a.row_id - 1, Removeddate)
from
(
select Row_number() over (partition by namepart,Removeddate order by namepart) as row_ID,Namepart,RemovedDate
from #tmp
) a

select * from #tmp
/*
RESULTS
namepart	Removeddate
1	2012-03-01 15:10:36.843
1	2012-03-01 15:10:37.843
1	2012-03-01 15:10:38.843
1	2012-03-01 15:10:39.843
1	2012-03-01 15:10:40.843
1	2012-03-01 15:10:41.843
1	2012-03-01 15:10:42.843
*/

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-03-01 : 16:26:18

I believe this is because the millisecond is stored as a float and can't be reproduced with precision, but it is only my guess.

[edit]
I see lamprey already addressed this with his post indicating that you do need a datetime2
[/edit]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

davematlynch
Starting Member

2 Posts

Posted - 2012-03-01 : 18:26:16

Thanks Vinnie881

Ran into the same problem with milliseconds
the field is not a datetime2 field so i just update time by seconds for all duplicate records

select *
from dbo.Constants a
join
(select NamePart, RemovedDate
from dbo.Constants
group by NamePart, RemovedDate
having count(*) > 1) b on a.RemovedDate = b.RemovedDate
and
a.NamePart = b.NamePart
order by a.RemovedDate, a.NamePart

Update a
set a.removeddate = dateadd(second, a.row_id - 1, RemovedDate)
from
(
select Row_number() over (partition by NamePart,RemovedDate order by namepart) as row_ID,Namepart,RemovedDate
from dbo.Constants
) a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources