SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 update count column
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 04/25/2008 :  07:26:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- Prepare sample data
CREATE TABLE	#Sample
		(
			Col1 INT,
			Col2 INT
		)

INSERT	#Sample
	(
		Col1
	)
SELECT	112 UNION ALL
SELECT	112 UNION ALL
SELECT	110 UNION ALL
SELECT	110 UNION ALL
SELECT	112 UNION ALL
SELECT	112 UNION ALL
SELECT	112

-- Display content before update
SELECT	*
FROM	#Sample

-- Prepare sequencing
DECLARE	@Col1 INT,
	@Col2 INT

CREATE CLUSTERED INDEX IX_Yak ON #Sample (Col1)

-- Do the update
UPDATE	#Sample
SET	@Col2 = Col2 = CASE WHEN @Col1 IS NULL OR @Col1 <> Col1 THEN 1 ELSE @Col2 + 1 END,
	@Col1 = Col1

-- Display content after update
SELECT	*
FROM	#Sample

-- Clean up
DROP INDEX	#Sample.IX_Yak



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

craigmacca
Posting Yak Master

141 Posts

Posted - 04/25/2008 :  07:31:51  Show Profile  Reply with Quote
ok thats surley to big for what i need??

if i did this in asp or coldfusion i would do it like this

SELECT TreeNodeID, TreeNodeClassID, TreeID, ParentTreeNodeID, LeftExtent, RightExtent, Ordinal
FROM TreeNode
WHERE (TreeID = 204)
ORDER BY LeftExtent

COUNT = 1<<< DEFAULT COUNT

LOOP <<<LOOP THROUGH QUERY ABOVE

UPDATE TreeNode
SET Ordinal = COUNT

COUNT = COUNT +1
CLOSE LOOP

there must be a way in SQL to do the same???
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 04/25/2008 :  07:50:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
How many answers do you want?
If you don't like the answers you are given, keep Coldfusion as your primary tool.

As said many times before, you have given us too little information to further assist you.

We need

1) Complete table layout
2) Proper sample data
3) Expected output based on provided sample data

Or read http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
(this link have been given to you many many times).

On the other hand, to mimic your ColdFusion example above, try this
UPDATE		tn
SET		tn.Ordinal = x.Items
FROM		TreeNode AS tn
CROSS JOIN	(
			SELECT	COUNT(*) AS Items
			FROM	TreeNode
			WHERE	TreeID = 204
		) AS x
WHERE		tn.TreeID = 204



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

craigmacca
Posting Yak Master

141 Posts

Posted - 04/25/2008 :  07:58:19  Show Profile  Reply with Quote
ok thats looks like what i need, but now i get an error of

invalid object name tn

then it updates my code to

UPDATE tn
SET tn.Ordinal = x.Items
FROM TreeNode AS tn CROSS JOIN
(SELECT COUNT(*) AS Items
FROM TreeNode
WHERE (TreeID = 204)) AS x CROSS JOIN
tn
WHERE (tn.TreeID = 204)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 04/25/2008 :  08:02:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by craigmacca

UPDATE tn
SET tn.Ordinal = x.Items
FROM TreeNode AS tn CROSS JOIN
(SELECT COUNT(*) AS Items
FROM TreeNode
WHERE (TreeID = 204)) AS x CROSS JOIN
tn

WHERE (tn.TreeID = 204)

That is not what I posted.
Drop the second self-referenced CROSS JOIN.
UPDATE		tn
SET		tn.Ordinal = x.Items
FROM		TreeNode AS tn
CROSS JOIN	(
			SELECT	COUNT(*) AS Items
			FROM	TreeNode
			WHERE	TreeID = 204
		) AS x
WHERE		tn.TreeID = 204



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

Edited by - SwePeso on 04/25/2008 08:02:27
Go to Top of Page

craigmacca
Posting Yak Master

141 Posts

Posted - 04/25/2008 :  08:03:05  Show Profile  Reply with Quote
ok that worked but it set all rows to the total count

ie

122
122
122

insead of

1
2
3 to 122
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 04/25/2008 :  08:06:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Now you finally make some sense! Finally an explanation of what you want...

No, there is no easy way to do this in SQL Server 2000.
Still you haven't told us which indexes and so on that exists for your table.
How do you expect us to help if you do not give us the information we require?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 04/25/2008 :  08:09:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This is mostly the most efficient way to do this in SQL Server 2000
SELECT		IDENTITY(INT, 1, 1) AS RowID,
		TreeNodeID
INTO		#Stage
FROM		TreeNode
WHERE		TreeID = 204
ORDER BY	TreeNodeID

UPDATE		tn
SET		tn.Ordinal = s.RowID
FROM		TreeNode AS tn
INNER JOIN	#Stage AS s ON s.TreeNodeID = tn.TreeNodeID

DROP TABLE	#Stage


This example relies on that TreeNodeID is the primary key of TreeNode table.
We can't say because you haven't told us. We can only guess it is.


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

Edited by - SwePeso on 04/25/2008 08:10:37
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 04/25/2008 :  12:29:37  Show Profile  Reply with Quote
Do you care about the order? What are you going to use the rowcount for? What does your id column look like? Does it happen to look like a rowcount? Does your id column have a clustered index?


Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 08/11/2008 :  12:37:24  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
quote:
Originally posted by Peso

This is mostly the most efficient way to do this in SQL Server 2000
SELECT		IDENTITY(INT, 1, 1) AS RowID,
		TreeNodeID
INTO		#Stage
FROM		TreeNode
WHERE		TreeID = 204
ORDER BY	TreeNodeID

UPDATE		tn
SET		tn.Ordinal = s.RowID
FROM		TreeNode AS tn
INNER JOIN	#Stage AS s ON s.TreeNodeID = tn.TreeNodeID

DROP TABLE	#Stage


This example relies on that TreeNodeID is the primary key of TreeNode table.
We can't say because you haven't told us. We can only guess it is.


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




Hi Peso,

Sorry this is going back a bit.

Is SELECT INTO an efficient method for this? I was under the impression that a SELECT INTO added rows 1 at a time. Is it any better to create the #stage table first (with an identity column) and then insert into it using INSERT INTO #stage SELECT rather than SELECT x INTO #stage.



-------------
Charlie
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000