How to update rows with row numbers

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

How to update rows with row numbers

Author

Topic

bacha
Starting Member

2 Posts

Posted - 2008-11-25 : 10:08:25

Hi,
I am new to SQL. I have a table with 10 rows. How can I update the 2nd and 5th row by only giving the row number(2 and 5) of the row in the where clause of the update query?

Thanks in advance,
Bala

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 10:12:10

Are you using SQL Server 2005?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 11:32:09

sql 2005

UPDATE t
SET t.RowNo=t.Seq
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY yourorderfield) AS Seq,*
FROm Table)t
WHERE t.Seq IN (2,5)

sql 2000


UPDATE t
SET  t.RowNo=t.Seq
FROM (SELECT (SELECT COUNT(*) FROM Table WHERE yourorderfield<=r.yourorderfield) AS Seq,*
FROM Table r
)t
WHERE t.Seq IN (2,5)

bacha
Starting Member

2 Posts

Posted - 2008-11-25 : 11:41:11

Hello,
Thank you for the replies.

@Peso
I am using an SQL 2000

@Visakh
If you don't mind, can you explain me the query you have written so that I can get a better picture.

Thanks
Bala

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 11:44:11

quote:
Originally posted by bacha

Hello,
Thank you for the replies.

@Peso
I am using an SQL 2000

@Visakh
If you don't mind, can you explain me the query you have written so that I can get a better picture.

Thanks
Bala

what i'm doing is generating a rownumber by checking count of records that has value less than or equal to current records value (this will give its position). then using it to update table field.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources