Updating Table using a Cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Updating Table using a Cursor?

Author

Topic

23Soul
Starting Member

3 Posts

Posted - 2007-07-30 : 08:14:14

Hi All

I've had a search through the Forums, but can't seem to find a similar problem / solution to the issue I have, so would appreciate any help anyone could offer, as I'm pretty much stuck...

I have an existing table (created from a query on another table), which includes the following columns and info:

MMSINumber ReportingInterval PositionLost
-------------------------------------------------------------

0 20 0
1 30 0
1 170 0
1 30 0
2 50 0
2 40 0
2 180 0
2 160 0
3 30 0
3 150 0
3 170 0
3 150 0
3 110 0

This information relates to multiple position 'reports' made by numerous entities over a period of time, with the 'MMSINumber' values indicating the individual entities in question. The information is ordered by Datetime (column not shown above). The 'PositionLost' column is populated by default with a value of '0' from the previous query (although this could obviously be altered to populate with NULL values on table creation).

What I need to do is update the table to insert a tally / increment under the 'PositionLost' column, based on the following criteria:

- The tally / increment to be calculated for each distinct value under 'MMSINumber'.
- Default value for 'PositionLost' is 0.
- If 'ReportingInterval' value is >= 100, then increment 'PositionLost' value by 1.
- If 'ReportingInterval' value is < 100, then 'PositionLost' value equals the last 'PositionLost' value for that 'MMSINumber' value, or 0 if no other records for that 'MMSINumber' value are in the previous rows.

The objective is to obtain an updated table with values looking like this:

MMSINumber ReportingInterval PositionLost
-------------------------------------------------------------
0 20 0
1 30 0
1 170 1
1 30 1
2 50 0
2 40 0
2 180 1
2 160 2
3 30 0
3 150 1
3 170 2
3 150 3
3 110 4

Now I thought a cursor might be the best way to undertake this (through fetching the individual rows into variables), but am obviously doing something (very) stupid here, as when the query is executed, the 'PositionLost' values in the table are all updated with a value of 4730 !

The portion of code I currently am trying to work with is:


DECLARE MMSICursor CURSOR
FOR
	SELECT GISResults.MMSINumber,
		GISResults.ReportingInterval
	FROM GISResults

OPEN MMSICursor

FETCH NEXT FROM MMSICursor
	INTO @MMSICounter, @IntervalCounter
FETCH NEXT FROM MMSICursor
	INTO @MMSICounter2, @IntervalCounter2

WHILE @@FETCH_STATUS = 0
BEGIN
	IF ( @IntervalCounter >= 100 ) AND ( @MMSICounter = @MMSICounter2 )
	BEGIN
		UPDATE GISResults
		SET @PositionLostCounter = GISResults.PositionLost = @PositionLostCounter + 1
		SET @MMSICounter = @MMSICounter2
		SET @IntervalCounter = @IntervalCounter2
		FETCH NEXT FROM MMSICursor
		INTO @MMSICounter2, @IntervalCounter2
	END
	IF ( @IntervalCounter < 100 ) AND ( @MMSICounter = @MMSICounter2 )
	BEGIN
		UPDATE GISResults
		SET GISResults.PositionLost = @PositionLostCounter
		SET @MMSICounter = @MMSICounter2
		SET @IntervalCounter = @IntervalCounter2
		FETCH NEXT FROM MMSICursor
		INTO @MMSICounter2, @IntervalCounter2
	END
	IF ( @MMSICounter <> @MMSICounter2 )
	BEGIN
		SET @PositionLostCounter = 0
		SET @MMSICounter = @MMSICounter2
		SET @IntervalCounter = @IntervalCounter2
		FETCH NEXT FROM MMSICursor
		INTO @MMSICounter2, @IntervalCounter2
	END

END

CLOSE MMSICursor
DEALLOCATE MMSICursor

There is currently no column set with a primary key, which I guess will have something to do with it, as I'll need to tell the system where to insert the values?

Any help or pointers on this would be appreciated - apologies for the long-winded post!

Thanks

Steve

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 08:54:04

What defines the ORDER of records? The three column present can't be used to tell that.
Do you have an IDENTITY or datetime column to define your order?

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

23Soul
Starting Member

3 Posts

Posted - 2007-07-30 : 09:02:25

quote:
Originally posted by Peso

What defines the ORDER of records? The three column present can't be used to tell that.
Do you have an IDENTITY or datetime column to define your order?

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

Hi

Yes... as I mentioned in the original post (you might not have noticed, the post was a bit or a mind-blurt

), the information is ordered by Datetime, which is contained in a separate column (not shown in my example).

Cheers

Steve

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 09:02:44

[code]-- Prepare sample data
DECLARE @Sample TABLE (ID INT IDENTITY PRIMARY KEY, MMSINumber INT, ReportingInterval INT, PositionLost INT)

INSERT @Sample
SELECT 0, 20, 0 UNION ALL
SELECT 1, 30, 0 UNION ALL
SELECT 1, 170, 0 UNION ALL
SELECT 1, 30, 0 UNION ALL
SELECT 2, 50, 0 UNION ALL
SELECT 2, 40, 0 UNION ALL
SELECT 2, 180, 0 UNION ALL
SELECT 2, 160, 0 UNION ALL
SELECT 3, 30, 0 UNION ALL
SELECT 3, 150, 0 UNION ALL
SELECT 3, 170, 0 UNION ALL
SELECT 3, 150, 0 UNION ALL
SELECT 3, 110, 0

-- Before
SELECT * FROM @Sample

-- Stage the data
DECLARE @Number INT,
@Lost INT

SELECT @Number = MMSINumber,
@Lost = 0
FROM @Sample
WHERE ID = 1

UPDATE @Sample
SET @Lost = PositionLost = CASE
WHEN MMSINumber = @Number AND ReportingInterval >= 100 THEN @Lost + 1
WHEN MMSINumber = @Number AND ReportingInterval < 100 THEN @Lost
ELSE 0
END,
@Number = MMSINumber

-- After
SELECT * FROM @Sample[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 09:04:32

[code]-- Prepare sample data
CREATE TABLE #Sample
(
dt DATETIME,
MMSINumber INT,
ReportingInterval INT,
PositionLost INT
)

INSERT #Sample
SELECT '20070601', 0, 20, 0 UNION ALL
SELECT '20070602', 1, 30, 0 UNION ALL
SELECT '20070604', 1, 170, 0 UNION ALL
SELECT '20070606', 1, 30, 0 UNION ALL
SELECT '20070401', 2, 50, 0 UNION ALL
SELECT '20071231', 2, 40, 0 UNION ALL
SELECT '20080101', 2, 180, 0 UNION ALL
SELECT '20080601', 2, 160, 0 UNION ALL
SELECT '20040229', 3, 30, 0 UNION ALL
SELECT '20040301', 3, 150, 0 UNION ALL
SELECT '20050906', 3, 170, 0 UNION ALL
SELECT '20051231', 3, 150, 0 UNION ALL
SELECT '20070730', 3, 110, 0

CREATE CLUSTERED INDEX IX_Sample ON #Sample (MMSINumber, dt)

-- Before
SELECT * FROM #Sample

-- Stage the data
DECLARE @Number INT,
@Lost INT

UPDATE #Sample
SET @Lost = PositionLost = CASE
WHEN @Number IS NULL THEN 0
WHEN MMSINumber = @Number AND ReportingInterval >= 100 THEN @Lost + 1
WHEN MMSINumber = @Number AND ReportingInterval < 100 THEN @Lost
ELSE 0
END,
@Number = MMSINumber

-- After
SELECT * FROM #Sample

DROP TABLE #Sample[/code]

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

23Soul
Starting Member

3 Posts

Posted - 2007-07-30 : 11:09:58

That's great - thanks for the assist.

Guess I didn't need to go near a cursor at all then!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 11:28:16

No, just add a proper clustered index, and go!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources