Update w/ counter - 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)

Update w/ counter

Author

Topic

phatchico
Starting Member

3 Posts

Posted - 2007-10-17 : 13:07:16

I have 2 fields. ID and visit_id. I'm trying to get a count based on these two fields, based on the ID followed by the visit_id, to give me a visit# (the counter) Where ID is used every time an individual visits, but visit_id is a running total of visitors. I would like the result of the visit# column. Ex.

ID visit_id visit#
A 23 1
B 45 1
A 67 2
B 99 2
C 101 1
B 102 3

I have a working query but have tried many different ways to throw it into an update statement with no success:

(Select id, visit_id,(Select count(*) from table as x
where x.id = table.id
and x.visit_id <= zz_book.visit_id
)
From table)

Any help would be appreciated. Thanks in advanced.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-17 : 13:12:58

Not sure what you have tried..


Declare @T Table (ID Varchar(10),visit_id int, visit# int)
Insert into @T 
SELECT 'A' ,23 ,0 union all
SELECT 'B' ,45 ,0 union all
SELECT 'A' ,67 ,0 union all
SELECT 'B' ,99 ,0 union all
SELECT 'C' ,101 ,0 union all
SELECT 'B' ,102 ,0

Select * from @T 

update T
Set T.visit# = (Select count(*) from @t as x
	where x.id = T.id
	and x.visit_id <= T.visit_id
	) 
From @T T

Select * from @T

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

phatchico
Starting Member

3 Posts

Posted - 2007-10-17 : 13:23:39

Lol, well thats frustrating. I used your update statament which is almost identical to one that I had tried many times only to have an error thrown in my face. The only difference I saw was you added the table name to the front of the Set T.visit#...oh well the subtle things you have to learn. thanks for the help!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 02:08:53

You are using SQL Server 2005, right?

DECLARE	@Sample TABLE (ID CHAR(1), VisitID INT, VisitSeq INT)

INSERT	@Sample
SELECT	'A', 23, NULL UNION ALL
SELECT	'B', 45, NULL UNION ALL
SELECT	'A', 67, NULL UNION ALL
SELECT	'B', 99, NULL UNION ALL
SELECT	'C', 101, NULL UNION ALL
SELECT	'B', 102, NULL

SELECT * FROM @Sample

UPDATE	s
SET	s.VisitSeq = s.NewVisitSeq
FROM	(
		SELECT	VisitSeq,
			ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VisitID) AS NewVisitSeq
		FROM	@Sample
	) AS s

SELECT * FROM @Sample

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