February 6, 2014 at 8:39 am
I would like to know how to run an update or delete query and have a chance to commit or not. Here is the issue. When I want to run a delete query or update query on a table there is no chance to know how many records are being affected before running the query. For example I am running an update query and I type in my query and be able to click and get a message that says X number of records affected and if the number match up then I can commit. Same with delete
Is there a way I can add some script to the end of my query that will allow me to do this.
Please let me know and your help is appreciated.
Jeff
February 6, 2014 at 8:45 am
BEGIN TRAN
DELETE [tablename]
WHERE [some contitions]
IF @@ROWCOUNT < 10
COMMIT TRAN
ELSE
ROLLBACK TRAN
February 6, 2014 at 8:48 am
jayoub (2/6/2014)
I would like to know how to run an update or delete query and have a chance to commit or not. Here is the issue. When I want to run a delete query or update query on a table there is no chance to know how many records are being affected before running the query. For example I am running an update query and I type in my query and be able to click and get a message that says X number of records affected and if the number match up then I can commit. Same with deleteIs there a way I can add some script to the end of my query that will allow me to do this.
Please let me know and your help is appreciated.
You could run a select query using the same logic you would use for the update\delete before you ran the update\delete. That would bring back all the rows that would be affected.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 6, 2014 at 8:54 am
BWFC (2/6/2014)
jayoub (2/6/2014)
I would like to know how to run an update or delete query and have a chance to commit or not. Here is the issue. When I want to run a delete query or update query on a table there is no chance to know how many records are being affected before running the query. For example I am running an update query and I type in my query and be able to click and get a message that says X number of records affected and if the number match up then I can commit. Same with deleteIs there a way I can add some script to the end of my query that will allow me to do this.
Please let me know and your help is appreciated.
You could run a select query using the same logic you would use for the update\delete before you ran the update\delete. That would bring back all the rows that would be affected.
Yeah, if you can guarantee that nothing can insert into table beween "showing result of select" and confirming Delete button, you can go with this idea. But how are you going to guarantee that nothing touches your table? Without holding a lock on it, it will be hard...
February 6, 2014 at 9:00 am
Eugene Elutin (2/6/2014)
BWFC (2/6/2014)
jayoub (2/6/2014)
I would like to know how to run an update or delete query and have a chance to commit or not. Here is the issue. When I want to run a delete query or update query on a table there is no chance to know how many records are being affected before running the query. For example I am running an update query and I type in my query and be able to click and get a message that says X number of records affected and if the number match up then I can commit. Same with deleteIs there a way I can add some script to the end of my query that will allow me to do this.
Please let me know and your help is appreciated.
You could run a select query using the same logic you would use for the update\delete before you ran the update\delete. That would bring back all the rows that would be affected.
Yeah, if you can guarantee that nothing can insert into table beween "showing result of select" and confirming Delete button, you can go with this idea. But how are you going to guarantee that nothing touches your table? Without holding a lock on it, it will be hard...
Good point. Won't that potentially be the case if there's any kind of lag between finding out how many rows will be affected and running the update\delete?
if the number match up then I can commit
That suggests that there will have to be some check taking place?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 6, 2014 at 9:09 am
Actually, it is not very clear of what OP want's and what he is talking about.
I cannot see how SQL script/proc can ask user to confirm for proceeding with action...
If he is talking about some UI app, then I probably would do it a bit differently, as holding open transaction while user is drinking tea and thinking about "to be or not to be..." is not very good idea.
There many different ways depending on what actully required...
February 6, 2014 at 11:01 am
Eugene Elutin (2/6/2014)
Actually, it is not very clear of what OP want's and what he is talking about.I cannot see how SQL script/proc can ask user to confirm for proceeding with action...
If he is talking about some UI app, then I probably would do it a bit differently, as holding open transaction while user is drinking tea and thinking about "to be or not to be..." is not very good idea.
There many different ways depending on what actully required...
Again, good point. I assume there is more to it than was originally posted. I suppose my answer should actually have been 'it depends'!
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 6, 2014 at 12:19 pm
The rollback tran works perfectly for me. Thank you very much.
Not sure why you guys call me OP but not concerned.
Jeff
February 6, 2014 at 1:03 pm
jayoub (2/6/2014)
... if the number match up ...
What do you mean by this, Jeff? Match up with what exactly?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 6, 2014 at 1:16 pm
jayoub (2/6/2014)
The rollback tran works perfectly for me. Thank you very much.Not sure why you guys call me OP but not concerned.
OP stands for Original Poster. That's a way to avoid problems with confusing/difficult names or nicknames.
February 18, 2014 at 7:34 am
How about a join to OUTPUT inserted.* or deleted.* ?
February 18, 2014 at 7:58 am
jayoub (2/6/2014)
The rollback tran works perfectly for me. Thank you very much.
Just bear in mind that performs the operation and then undoes it. If a lot of rows are affected by the delete, the delete could take some time and the rollback could take even longer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2014 at 8:38 am
Thanks for the reply
That's a good point. As long as the rollback occurs I am fine with the time it takes. Is there any way that the rollback could fail?
I am taking a programming class and I would like to build something that could give the user a chance to view a rowcount before the update or delete would even happen
When i worked as a DBA for a Property Management company we used a software called MRI. It had a great feature in the software that i could use to query the tables directly when needed to do back door changes. What was very nice is that it would prompt you with "X number of records affected" and you could click Yes to continue or No to cancel; this saved my bacon on plenty of occasions.
I wish there was a way to do this with either SSMS or I may try to build out something as a project for my programming class.
Any ideas would be helpful.
Again thanks for the reply. I love SSC!
Jeff
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply