specify the row of query result

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

SQL Server 2005 Forums

Transact-SQL (2005)

specify the row of query result

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2009-06-06 : 01:02:59

I have a select query which have to update every row of the result, but i dont know how many rows the results have and i dont know how to specify the rows to update them.
i thougt i should count the rows (COUNT*) and add a field of row_number to each row, then i can update each row depends on its row_number, but i dont know how to insert row number to rows.
please help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-06 : 01:53:26

quote:
I have a select query which have to update every row of the result, but i dont know how many rows the results have and i dont know how to specify the rows to update them.
i thougt i should count the rows (COUNT*) and add a field of row_number to each row, then i can update each row depends on its row_number, but i dont know how to insert row number to rows

you don't need to know the number of rows to update them.

Just use a UPDATE command to update.


update t
set    somecol = somevalue
from   table1 t
where  anothercolumn = <some condition>

KH
[spoiler]Time is always against us[/spoiler]

Exir
Posting Yak Master

151 Posts

Posted - 2009-06-06 : 02:06:47

I dont know any value of columns to put in WHERE section, becouse of this i suggested to add number of rows to each row to update them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-06 : 02:31:54

if you want to update all rows in the table then you don't need the WHERE clause

KH
[spoiler]Time is always against us[/spoiler]

Exir
Posting Yak Master

151 Posts

Posted - 2009-06-06 : 02:46:17

I dont want to update them with same value. values are different, no problem which row get which value but each row of the select result should get a value.

Is there any way to assign row number to each row of the select result ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-06 : 02:49:31

quote:
Originally posted by Exir

I dont want to update them with same value. values are different, no problem which row get which value but each row of the select result should get a value.

Is there any way to assign row number to each row of the select result ?

can you explain more in detail what do you want to do here. Provide your table structure, sample data and the required result

KH
[spoiler]Time is always against us[/spoiler]

Exir
Posting Yak Master

151 Posts

Posted - 2009-06-06 : 03:35:48

I have a select command which have several rows in result, inorder to not having any information about rows value, i have to find a way to make rows distinct to be able to do any operation i want to them.

just tell me if it is possible to assign a number to each row to be able to work on rows separately and use row number in WHERE part

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-06 : 03:47:44

quote:
Originally posted by Exir

I have a select command which have several rows in result, inorder to not having any information about rows value, i have to find a way to make rows distinct to be able to do any operation i want to them.

just tell me if it is possible to assign a number to each row to be able to work on rows separately and use row number in WHERE part

Yes. It is possible to assign a number to each row. Just check out row_number() in BOL

KH
[spoiler]Time is always against us[/spoiler]

Exir
Posting Yak Master

151 Posts

Posted - 2009-06-06 : 04:03:37

Thank you.
I wrote the query like this and now have row_number for each row:

select row_number() over (order by L_Name asc) as rownum, * from Guarntings WHERE person_no=817057

Now how can i write a query to select a row which its rownum=1 ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-06 : 04:10:09

[code]
select *
from
(
select row_number() over (order by L_Name asc) as rownum, * from Guarntings WHERE person_no=817057
) a
where a.rownum=1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Exir
Posting Yak Master

151 Posts

Posted - 2009-06-06 : 04:14:52

Thank you very much like always

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources