Microsoft SQL Server forum

Author

Update n rows

Khan

2006-03-22, 9:28 am

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.

Jens

2006-03-22, 9:28 am

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

-Jens Suessmeyer.

David Portas

2006-03-22, 9:28 am

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
--

Anith Sen

2006-03-22, 11:30 am

Look up SET ROWCOUNT in SQL Server Books Online.

--
Anith

Erland Sommarskog

2006-03-22, 8:33 pm

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

Khan

2006-03-25, 3:27 am

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.

David Portas

2006-03-25, 3:27 am

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
--

Erland Sommarskog

2006-03-25, 7:40 am

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

Sponsored Links