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 likei = 1loopupdate tableset column = iwhere column = 113i=i+1loopjust 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. |
|
|
craigmacca
Posting Yak Master
142 Posts |
Posted - 2008-04-24 : 07:37:35
|
ok socolumn1, column2112,1112,2112,3112,4112,5so its just need to update column2 and add the count |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-24 : 07:43:37
|
You only have these two columns? |
|
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-24 : 07:47:03
|
where clause? what is your full query then? |
|
|
craigmacca
Posting Yak Master
142 Posts |
Posted - 2008-04-24 : 07:50:50
|
update tableset column2 = i <<< this needs to just a count from 1 onwardswhere column1 = 113 <<< this is a static number which i will enter |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-24 : 08:06:26
|
quote: Originally posted by craigmacca update tableset column2 = i <<< this needs to just a count from 1 onwardswhere 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 tSET t.Column2 =t1.RowNoFROM YourTable tINNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY idcolumn) as RowNo,*FROM YourTable)t1ON t1.idcolumn=t.idcolumn |
|
|
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 tSET t.Column2 = t.RowNoFROM ( 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" |
|
|
craigmacca
Posting Yak Master
142 Posts |
Posted - 2008-04-24 : 08:35:57
|
ok can i do it somehow this wayselect colID, col1, col2 from table1where col1 = 223order by col2loop colID <<< not sure how to loop in sql??set ID = colIDset i = 1update table1set col3 = iwhere colID = IDi=i+1end loop |
|
|
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 tSET t.Ordinal = t.RowNoFROM ( SELECT Ordinal, ROW_NUMBER() OVER (PARTITION BY TreeNodeID ORDER BY LeftExtent) AS RowNo FROM TreeNode WHERE (TreeID = 204) ORDER BY LeftExtent) ) AS t |
|
|
cruxmagi
Starting Member
38 Posts |
Posted - 2008-04-24 : 09:23:13
|
sql loopExample 1declare @max intset @max=0while @max<>10begin print 'count '+cast( @max as varchar) set @max = @max+1endExample2declare c1 cursorfor select columnId from yourTabledeclare @Id intopen c1fetch next from c1 into @idwhile @@fetch_status !=-1begin--Do what you wantfetch next from c1 into @idendclose c1deallocate c1 |
|
|
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 tSET t.Ordinal = t.RowNoFROM ( 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" |
|
|
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 tSET t.Column2 = t.RowNoFROM ( 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. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-04-25 : 05:28:05
|
quote: Originally posted by cruxmagi sql loopExample 1declare @max intset @max=0while @max<>10begin print 'count '+cast( @max as varchar) set @max = @max+1endExample2declare c1 cursorfor select columnId from yourTabledeclare @Id intopen c1fetch next from c1 into @idwhile @@fetch_status !=-1begin--Do what you wantfetch next from c1 into @idendclose c1deallocate c1
No loopselect 'count '+cast(number as varchar(3)) from master..spt_valueswhere type='p' and number<10MadhivananFailing to plan is Planning to fail |
|
|
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, OrdinalFROM TreeNodeWHERE (TreeID = 204)ORDER BY LeftExtent |
|
|
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, OrdinalFROM TreeNodeWHERE (TreeID = 204)ORDER BY LeftExtent
are you using sql 2005 with compatability level 90? |
|
|
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? |
|
|
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. |
|
|
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? |
|
|
Next Page
|