Update Top 2000 - something like that.

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!
LVL 2
Starr DuskkASP.NET VB.NET DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
found answer:
update top (100) table1 set field1 = 1

parentheses are required.
0
ste5anSenior DeveloperCommented:
Just a comment:

Such an update will change arbitrary rows.
0
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
that was just an example. ;)
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
found answer:
update top (100) table1 set field1 = 1

parentheses are required.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
Again: The rows changed by that statement are arbitrary.
0
awking00Information Technology SpecialistCommented:
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.
0
Starr DuskkASP.NET VB.NET DeveloperAuthor 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
0
awking00Information Technology SpecialistCommented:
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?
0
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
I'm done with this question. I have it all set. I just update the top (x) on a loop.

thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.