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
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

craigmacca
Posting Yak Master

139 Posts

Posted - 04/24/2008 :  07:30:43  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 04/24/2008 :  07:34:33  Show Profile  Reply with Quote
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

139 Posts

Posted - 04/24/2008 :  07:37:35  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 04/24/2008 :  07:43:37  Show Profile  Reply with Quote
You only have these two columns?
Go to Top of Page

craigmacca
Posting Yak Master

139 Posts

Posted - 04/24/2008 :  07:45:32  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 04/24/2008 :  07:47:03  Show Profile  Reply with Quote
where clause? what is your full query then?
Go to Top of Page

craigmacca
Posting Yak Master

139 Posts

Posted - 04/24/2008 :  07:50:50  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 04/24/2008 :  07:59:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
52249 Posts

Posted - 04/24/2008 :  08:06:26  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 04/24/2008 :  08:32:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

139 Posts

Posted - 04/24/2008 :  08:35:57  Show Profile  Reply with Quote
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

139 Posts

Posted - 04/24/2008 :  09:09:40  Show Profile  Reply with Quote
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

India
38 Posts

Posted - 04/24/2008 :  09:23:13  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 04/24/2008 :  09:32:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
52249 Posts

Posted - 04/24/2008 :  11:24:46  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 04/25/2008 :  05:28:05  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

139 Posts

Posted - 04/25/2008 :  06:42:07  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 04/25/2008 :  06:55:55  Show Profile  Reply with Quote
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

139 Posts

Posted - 04/25/2008 :  06:59:39  Show Profile  Reply with Quote
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

India
52249 Posts

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

craigmacca
Posting Yak Master

139 Posts

Posted - 04/25/2008 :  07:17:08  Show Profile  Reply with Quote
yes 2000, so how would i insert a row count in to a column?
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next 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.14 seconds. Powered By: Snitz Forums 2000