Update Top 2000 - something like that.

Starr Duskk
Starr Duskk used Ask the Experts™
on
SQL Server Database:

I need to update by top X like:

Update Top 2000 set ......

I don't find an example and when I try it errors. Is there something similar?

I am getting a time out on my update so I want to do it in pieces. Any ideas?

thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Starr DuskkASP.NET VB.NET Developer

Author

Commented:
found answer:
update top (100) table1 set field1 = 1

parentheses are required.
ste5anSenior Developer

Commented:
Just a comment:

Such an update will change arbitrary rows.
Starr DuskkASP.NET VB.NET Developer

Author

Commented:
that was just an example. ;)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ASP.NET VB.NET Developer
Commented:
found answer:
update top (100) table1 set field1 = 1

parentheses are required.
ste5anSenior Developer

Commented:
Again: The rows changed by that statement are arbitrary.
awking00Information Technology Specialist

Commented:
So after you update your first 2000 piece, how do you determine what the second 2000 piece should be? Ste5an is absolutely right so the next 2000 piece could contain some, none of all of the first 2000 piece. What does the update statement look like that times out? It might be modified to perform without a time out.
Starr DuskkASP.NET VB.NET Developer

Author

Commented:
I totally understand and I appreciate your feedback, but as I said, it was just from the example that I found showing that the parenthesis were required.

I will actually use it like so:

update top (2000) set fieldname = null where fieldname is not null
awking00Information Technology Specialist

Commented:
So your original update query that timed out was essentially -
update table set fieldname = null where fieldname is not null?
How many rows are in your table and what is the approximate percentage of null fieldnames and non-null fieldnames?
Starr DuskkASP.NET VB.NET Developer

Author

Commented:
I'm done with this question. I have it all set. I just update the top (x) on a loop.

thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial