| Author |
 Topic  |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
 Posted - 04/25/2008 : 07:26:16
|
-- 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" |
 |
|
|
craigmacca
Posting Yak Master
132 Posts |
Posted - 04/25/2008 : 07:31:51
|
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??? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/25/2008 : 07:50:02
|
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 thisUPDATE 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" |
|
|
|
craigmacca
Posting Yak Master
132 Posts |
Posted - 04/25/2008 : 07:58:19
|
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) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/25/2008 : 08:02:04
|
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 |
|
|
|
craigmacca
Posting Yak Master
132 Posts |
Posted - 04/25/2008 : 08:03:05
|
ok that worked but it set all rows to the total count
ie
122
122
122
insead of
1
2
3 to 122 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/25/2008 : 08:06:49
|
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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/25/2008 : 08:09:29
|
This is mostly the most efficient way to do this in SQL Server 2000SELECT 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 |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 04/25/2008 : 12:29:37
|
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?
|
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3438 Posts |
Posted - 08/11/2008 : 12:37:24
|
quote:
Originally posted by Peso
This is mostly the most efficient way to do this in SQL Server 2000SELECT 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 |
|
|
|
Topic |
|
update count column
