Update query

Question

Post reply

Update query

nitin jain-250047

SSC-Addicted

Points: 401
More actions
July 2, 2007 at 11:38 pm

#196829

hi experts,
is there any way to find how many data will update in query before updating them in sql server, i dont want to use select statement bcos its take time.
thanx

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

goodguy SSCertifiable Points: 5456 More actions · Answer 1

Could you be a bit more specific? What are you updating (or how) if you do not know the data?

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

nitin jain-250047 SSC-Addicted Points: 401 More actions · Answer 2

i m going to update some risky data so in that case i just want to confirm first that how many data r going to update, i can use the ROLLBACK & COMMIT command but i just want to know is ther any way to do this or any tool available for this

thanx

goodguy SSCertifiable Points: 5456 More actions · Answer 3

How will your UPDATE statement be passed - using a recordset object or Execute function?

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

nitin jain-250047 SSC-Addicted Points: 401 More actions · Answer 4

no dear i m not using any recordset object i just wann to do this directly thru SQL server/enterprise manager or QA

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

Just do a select on the criteria that you are going to update which should fetch the number of rows to be affected.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Hi Nitin

If you're going to update some risky data then you would be well advised to check beforehand that everything will work - if you make a mistake and update all 100 million rows instead of the 22 you intended, it will take far longer to put right than the length of time it would take to run a SELECT. You don't have to run a SELECT - you could run your update with something like

SET mycolumn = mycolumn

so nothing is changed.

How about posting your update statement, this will give us a better idea of where you're coming from?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

You could change the transaction level, do the update and then read the number of uncommited records and commit the transaction.

N 56°04'39.16"
E 12°55'05.25"

nitin jain-250047 SSC-Addicted Points: 401 More actions · Answer 8

thanx for yr reply byt could u pls tell me what is procedure to chg the transaction lvl

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 9

Use the SELECT to find out... doesn't matter how much time it takes... it's far better to know ahead of time than fixing "risky data" or having a large rollback occur.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)