Complex Update Query - 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 2000 Forums

Transact-SQL (2000)

Complex Update Query

Author

Topic

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-10-03 : 05:10:51

Hi guys,
Here is requirement
Table with 2 Fields with actual data.

ID Seq Output (What I want)
1 1 1
2 2 2
3 2 2
4 2 2
5 3 3
6 4 4
7 4 4
8 2 5
9 2 5
10 2 5
11 3 6

I know the output is possible through a cursor loop, but looping through 100000 records taking ages.

I really appreciate that if some one could give me an idea how to achieve requested output using SQL..

Many Thanks

SKR

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 05:12:25

You've posted in the SQL 2000 forum, can I just check you are NOT using SQL 2005 before answering?

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 05:19:26

See if this fit you
http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 05:23:59

Or this simple and very fast method, which work on both SQL Server 2000 and SQL Server 2005.
The trick is to have the primary key (clustered index) over ID column.

-- Prepare sample data
DECLARE	@Sample TABLE (ID INT IDENTITY(1, 1) PRIMARY KEY, Seq INT, Output INT)

INSERT	@Sample
	(
		Seq
	)
SELECT	1 UNION ALL
SELECT	2 UNION ALL 
SELECT	2 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	4 UNION ALL
SELECT	4 UNION ALL
SELECT	2 UNION ALL
SELECT	2 UNION ALL
SELECT	2 UNION ALL
SELECT	3

-- Prepare staging controls
DECLARE	@LastSeq INT,
	@Output INT

SELECT TOP 1	@LastSeq = Seq,
		@Output = 1
FROM		@Sample
ORDER BY	ID

UPDATE	@Sample
SET	@Output = Output =	CASE
					WHEN Seq = @LastSeq THEN @Output
					ELSE @Output + 1
				END,
	@LastSeq = Seq

-- Show the expected output
SELECT	ID,
	Seq,
	Output
FROM	@Sample

E 12°55'05.25"
N 56°04'39.16"

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-10-03 : 15:42:35

Kristen, I am using SQL 2000.
Peso, what should I say to you.. You are genius. I love you..
Thanks worked.. Thanks a lot.

SKR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources