Increment during UPDATE - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Increment during UPDATE

Author

Topic

GaryAdams
Starting Member

5 Posts

Posted - 2008-09-23 : 10:57:23

Hello,

I'm trying to update a table I have with an incremental date but I'm unsure of the best way to do it. The table is as folows:

ID Name PublishDate
1 Test1 NULL
2 Test2 NULL
3 Test3 NULL
4 Test4 NULL
5 Test5 NULL

And I want to put in a start date, today's date for example for the publish date of ID1 and then I want to put a publish date of today's date + 1 for ID2 and so on.

So the example tale would then look like this after the update:

ID Name PublishDate
1 Test1 09/23/2008 00:00:00 AM
2 Test2 09/24/2008 00:00:00 AM
3 Test3 09/25/2008 00:00:00 AM
4 Test4 09/26/2008 00:00:00 AM
5 Test5 09/27/2008 00:00:00 AM

I have a table with about 400 rows that I want to do this to.

I tried playing around with ROW_Number() but didn't get anywhere.

Any help appretiated, thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 11:15:51

may be this

Update Table
SET PublishDate=DATEADD(dd,ID-1,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))

GaryAdams
Starting Member

5 Posts

Posted - 2008-09-23 : 12:00:11

That seemed to work, thanks!

The only problem is that the ID column doesn't count 1,2,3,4,5 as some records have been removed so it's more like 1,3,6,9 but I think all I need to do is add a new "count" column and populate this with 1,2,3,4,5 and so on... Now I just need to find out how to do that! :)

Thanks again!

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-23 : 12:40:49

No new column required. Use RANK() OVER (ORDER BY ID).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 12:42:46

quote:
Originally posted by GaryAdams

That seemed to work, thanks!

The only problem is that the ID column doesn't count 1,2,3,4,5 as some records have been removed so it's more like 1,3,6,9 but I think all I need to do is add a new "count" column and populate this with 1,2,3,4,5 and so on... Now I just need to find out how to do that! :)

Thanks again!

Use ROW_NUMBER() function to generate the count no.

GaryAdams
Starting Member

5 Posts

Posted - 2008-09-24 : 04:52:53

Thanks guys!

bjoerns, could you explain how to use RANK() OVER (ORDER BY ID) in this scenario, I've tried several things but can only produce errors. :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 05:02:54

quote:
Originally posted by GaryAdams

Thanks guys!

bjoerns, could you explain how to use RANK() OVER (ORDER BY ID) in this scenario, I've tried several things but can only produce errors. :(

i think what you want is this

UPDATE t
SET t.PublishDate=DATEADD(dd,DATEDIFF(dd,0,GETDATE())+(t1.Seq-1),0)
FROM yourtable t
INNER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Seq,*
FROM Yourtable)t1
ON t1.ID=t.ID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 05:11:02

[code]DECLARE @Sample TABLE
(
ID INT,
Name VARCHAR(20),
PublishDate DATETIME
)

INSERT @Sample
SELECT 1, 'Test1', NULL UNION ALL
SELECT 3, 'Test2', NULL UNION ALL
SELECT 5, 'Test3', NULL UNION ALL
SELECT 8, 'Test4', NULL UNION ALL
SELECT 9, 'Test5', NULL

SELECT *
FROM @Sample

UPDATE f
SET f.PublishDate = f.yak
FROM (
SELECT PublishDate,
DATEDIFF(DAY, 1 - ROW_NUMBER() OVER (ORDER BY ID), GETDATE()) AS yak
FROM @Sample
) AS f

SELECT *
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

GaryAdams
Starting Member

5 Posts

Posted - 2008-09-24 : 05:11:49

visakh16, Thank-you very much, that's exactly what I'm looking for - works like a charm! :)

GaryAdams
Starting Member

5 Posts

Posted - 2008-09-24 : 05:13:30

Peso, you just got beaten to it! But thanks for taking the time to help out too! ;)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 05:22:38

quote:
Originally posted by GaryAdams

visakh16, Thank-you very much, that's exactly what I'm looking for - works like a charm! :)

welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 05:34:06

quote:
Originally posted by GaryAdams

Peso, you just got beaten to it! But thanks for taking the time to help out too! ;)

Thank you. I had a concern about performance but you may not want to listen.

-- Create sample table
CREATE TABLE #Sample
		(
			ID INT PRIMARY KEY CLUSTERED,
			PublishDate DATETIME,
			PublishDate2 DATETIME
		)

-- Populate sample table
INSERT		#Sample
		(
			ID
		)
SELECT		CHECKSUM(NEWID())
FROM		sysobjects AS so1
CROSS JOIN	sysobjects AS so2

SELECT	COUNT(*) AS Records
FROM	#Sample

-- Initialize time testing
DECLARE	@time DATETIME

-- Peso
SET @time = GETDATE()

UPDATE	f
SET	f.PublishDate = f.yak
FROM	(
		SELECT	PublishDate,
			DATEDIFF(DAY, 1 - ROW_NUMBER() OVER (ORDER BY ID), GETDATE()) AS yak
		FROM	#Sample
	) AS f

SELECT	DATEDIFF(MILLISECOND, @time, GETDATE()) AS Peso

-- Visakh16
SET @time = GETDATE()

UPDATE		t
SET		t.PublishDate2 = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) + (t1.Seq - 1), 0)
FROM		#Sample t
INNER JOIN	(
			SELECT	ROW_NUMBER() OVER (ORDER BY ID) AS Seq,
				*
			FROM	#Sample
		) t1 ON t1.ID = t.ID

SELECT	DATEDIFF(MILLISECOND, @time, GETDATE()) AS Visakh16

DROP TABLE	#Sample

It seems Peso is about 3 times faster and only need 1/1000th the number of reads from the database (according to SQL Profiler).

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources