Update n rows

Hi,

I'm using table it has 100 rows, how can i update 10 or 15 rows at the
same time with single query. In real case, if user enters "5" in
textbox it means that the "5" rows will be updated.

In SAS i was using 'outobs' {outobs = n} which means n rows will be
affected.

Thanx.

Do you mean that you want to update the whole table, but want to do it
in 5-er chunks ?

-Jens Suessmeyer.

Khan  wrote:
> Hi,
>
> I'm using table it has 100 rows, how can i update 10 or 15 rows at the
> same time with single query. In real case, if user enters "5" in
> textbox it means that the "5" rows will be updated.
>
> In SAS i was using 'outobs' {outobs = n} which means n rows will be
> affected.
>
> Thanx.


That doesn't make much sense as you have described it. Exactly which N
rows should be updated? If you don't care which then I'd say the table
design was questionable - I mean if all rows are equal then why do you
need 100 of them? If you actually do care which rows get updated then
the answer is "use a WHERE clause". Example:

UPDATE your_table
SET col1 = 'something'
WHERE col2 BETWEEN 1 AND 5 ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Look up SET ROWCOUNT in SQL Server Books Online.

--
Anith

Khan (amir@programmer.net)  writes:
> I'm using table it has 100 rows, how can i update 10 or 15 rows at the
> same time with single query. In real case, if user enters "5" in
> textbox it means that the "5" rows will be updated.
>
> In SAS i was using 'outobs' {outobs = n} which means n rows will be
> affected.

As Anith said, you can use SET ROWCOUNT. In SQL 2005, you can also use
the TOP keyword to limit the update.

But as David said, the request as given makes little sense. It would
help if you included:

o  CREATE TABLE statement for your table.
o  INSERT statements with sample data.
o  The desired result given the sample.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

I'm using where condition e.g. " where assign = ' ' ". but this
condition is true for 100 rows, and i want to update only first 15
rows, otherwise all 100 rows will be updated which i don't want.
I tried SET ROWCOUNT but coud'nt get through.

Khan  wrote:
> I'm using where condition e.g. " where assign = ' ' ". but this
> condition is true for 100 rows, and i want to update only first 15
> rows, otherwise all 100 rows will be updated which i don't want.
> I tried SET ROWCOUNT but coud'nt get through.

Tables have no inherent order. "First 15 rows" doesn't make any sense
unless you explain what defines those first 15. Is there a date column
for example? What is/are the key(s) of your table? As we already
suggested, please post DDL and sample data and tell us what version of
SQL Server you are using. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Khan (amir@programmer.net)  writes:
> I'm using where condition e.g. " where assign = ' ' ". but this
> condition is true for 100 rows, and i want to update only first 15
> rows, otherwise all 100 rows will be updated which i don't want.
> I tried SET ROWCOUNT but coud'nt get through.

Try:

SET ROWCOUNT @n
INSERT #temp (...)
SELECT ... FROM tbl WHERE assign = ' '

SET ROWCOUNT 0

UPDATE tbl
SET    assign = 'X'
FROM   tbl
JOIN   #temp ON tbl.keycol = #temp.keycol

This presumes that the rows has a key that you can identify them with.

I also repeat the suggestion from my previous post, include:

o  CREATE TABLE statement for your table.
o  INSERT statements with sample data.
o  The desired result given the sample.

This increases your chances to get a useful response.

By the way, which version of SQL Server are you using?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx