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
 General SQL Server Forums
 New to SQL Server Programming
 update count column

Author  Topic 

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-04-24 : 07:30:43
Hi I have added a new column to my table, i need to run a query and update this colmn with a count, so like

i = 1

loop

update table
set column = i
where column = 113

i=i+1

loop

just not sure what the sql syntax is?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 07:34:33
You want to update it by autonumbered values? please give some sample data to make your question clear.
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-04-24 : 07:37:35
ok so

column1, column2
112,1
112,2
112,3
112,4
112,5

so its just need to update column2 and add the count
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 07:43:37
You only have these two columns?
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-04-24 : 07:45:32
no i have a id column aswell,

column2 is currently null, i need it to be a count specific to the where clause
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 07:47:03
where clause? what is your full query then?
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-04-24 : 07:50:50
update table
set column2 = i <<< this needs to just a count from 1 onwards
where column1 = 113 <<< this is a static number which i will enter
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-24 : 07:59:00
Clear as Mud!

UPDATE yt
SET yt.Column = Peso.MagicValue
FROM YourTable AS yt
INNER JOIN MagicBox AS Peso

Or read this blog post (as suggested to you before) and follow the steps/advice.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx



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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 08:06:26
quote:
Originally posted by craigmacca

update table
set column2 = i <<< this needs to just a count from 1 onwards
where column1 = 113 <<< this is a static number which i will enter


Last attempt! If this is not working for you we cant really help. You need to first provide clear information of what you exactly want or else you are really making it hard for somebody who is trying to help.


UPDATE t
SET t.Column2 =t1.RowNo
FROM YourTable t
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY idcolumn) as RowNo,
*
FROM YourTable)t1
ON t1.idcolumn=t.idcolumn
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-24 : 08:32:47
Don't make the update harder than necessary (even if this is not the algorithm OP wants)
UPDATE	t
SET t.Column2 = t.RowNo
FROM (
SELECT Column2,
ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY IdColumn) AS RowNo
FROM YourTable
) AS t



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

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-04-24 : 08:35:57
ok can i do it somehow this way

select colID, col1, col2
from table1
where col1 = 223
order by col2

loop colID <<< not sure how to loop in sql??

set ID = colID
set i = 1

update table1
set col3 = i
where colID = ID

i=i+1

end loop
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-04-24 : 09:09:40
ok i have tried that as below but cant get it to work any ideas???

UPDATE t
SET t.Ordinal = t.RowNo
FROM (
SELECT Ordinal,
ROW_NUMBER() OVER (PARTITION BY TreeNodeID ORDER BY LeftExtent) AS RowNo
FROM TreeNode
WHERE (TreeID = 204)
ORDER BY LeftExtent)
) AS t
Go to Top of Page

cruxmagi
Starting Member

38 Posts

Posted - 2008-04-24 : 09:23:13
sql loop

Example 1
declare @max int
set @max=0
while @max<>10
begin
print 'count '+cast( @max as varchar)
set @max = @max+1
end

Example2
declare c1 cursor
for select columnId from yourTable
declare @Id int
open c1
fetch next from c1 into @id
while @@fetch_status !=-1
begin
--Do what you want
fetch next from c1 into @id
end
close c1
deallocate c1
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-24 : 09:32:51
Don't use ORDER BY in the derived table. Things are so much easier of you post the actual error message.
UPDATE	t
SET t.Ordinal = t.RowNo
FROM (
SELECT Ordinal,
ROW_NUMBER() OVER (PARTITION BY TreeNodeID ORDER BY LeftExtent) AS RowNo
FROM TreeNode
WHERE TreeID = 204
) AS t



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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 11:24:46
quote:
Originally posted by Peso

Don't make the update harder than necessary (even if this is not the algorithm OP wants)
UPDATE	t
SET t.Column2 = t.RowNo
FROM (
SELECT Column2,
ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY IdColumn) AS RowNo
FROM YourTable
) AS t



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



Yup i understand. No need for the join.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-25 : 05:28:05
quote:
Originally posted by cruxmagi

sql loop

Example 1
declare @max int
set @max=0
while @max<>10
begin
print 'count '+cast( @max as varchar)
set @max = @max+1
end

Example2
declare c1 cursor
for select columnId from yourTable
declare @Id int
open c1
fetch next from c1 into @id
while @@fetch_status !=-1
begin
--Do what you want
fetch next from c1 into @id
end
close c1
deallocate c1


No loop

select 'count '+cast(number as varchar(3)) from master..spt_values
where type='p' and number<10


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-04-25 : 06:42:07
ok i get this error,

The OVER SQL construct or statement is not supported.

i dont understand why this is so hard, all i want is to insert a count for each row

can i not have a query like this then do a row count and insert that count?

SELECT TreeNodeID, TreeNodeClassID, TreeID, ParentTreeNodeID, LeftExtent, RightExtent, Ordinal
FROM TreeNode
WHERE (TreeID = 204)
ORDER BY LeftExtent
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 06:55:55
quote:
Originally posted by craigmacca

ok i get this error,

The OVER SQL construct or statement is not supported.

i dont understand why this is so hard, all i want is to insert a count for each row

can i not have a query like this then do a row count and insert that count?

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


are you using sql 2005 with compatability level 90?
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-04-25 : 06:59:39
think i might be 2003 but i cant check at the min,

can i not just have a select query then loop through the row count to update the column?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 07:14:38
2003? there is no such version of sql server. it must be 2000 then.
Go to Top of Page

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-04-25 : 07:17:08
yes 2000, so how would i insert a row count in to a column?
Go to Top of Page
    Next Page

- Advertisement -