To delete record using transaction

Question

0.00/5 (No votes)

See more:

I want to delete certain records from a table based on a condition. I used the following transaction script to achieve it.

SQL

BEGIN TRY
    BEGIN TRANSACTION
	
	DELETE from Consultation
	where SchoolID in
	(
	SELECT SchoolID 
	from Consultation 
	where [SchoolID]=7128
	)
	
    COMMIT TRAN 
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN 
END CATCH

When I execute the above statement it shows 0 rows affected. But when I used the following script

SQL

BEGIN TRY
    BEGIN TRANSACTION
	
	select * from Consultation
	where SchoolID in
	(
	SELECT SchoolID 
	from Consultation 
	where [SchoolID]=7128
	)
	
    COMMIT TRAN 
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN 
END CATCH

it shows me 3 rows of records.

The records are deleting when I tried deleting it manually. But I consider it is not the safer method to delete records from table. Can anyone please help me find out what the issue behind this.

I even tried using script to delete for some other schoolID and it is working perfectly but not for this schoolID alone. I am unable to find out the issue fix for this. Please help.

Posted 26-May-15 23:12pm

user 3008

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

F-ES Sitecore · Answer 1 · 2015-05-26T23:29:00

Isn't your query just

DELETE from Consultation WHERE SchoolID=7128

surely you don't need the sub-query? If you want to ensure records in a second table exist then use joins rather than "in" sub-queries.

http://stackoverflow.com/questions/439750/t-sql-selecting-rows-to-delete-via-joins[^]

Advay Pandya · Answer 2 · 2015-05-26T23:41:00

Solution 2

Please try to do one thing.

Please put some error description on catch block to see if there is any error occurred or not. In your query if any error will occur then it will rollback the transaction and returns "0 Rows Affected".

SQL

BEGIN CATCH
    IF @@TRANCOUNT > 0
        Print Error_Message()
        ROLLBACK TRAN
END CATCH

Please let me know if you have any concern or query.

Thanks
Advay Pandya

Posted 26-May-15 23:41pm

Advay Pandya

Comments

user 3008 27-May-15 6:44am

Thank you so much for your idea. I had an error in the query. I didn't delete in its dependent table of Consultation and hence it has rolledback

Advay Pandya 27-May-15 9:52am

Always welcome :)

W∴ Balboos, *GHB* · Answer 3 · 2015-05-27T07:20:00

Consider the following when using transactions:

If you are doing a SELECT (your second code block) - what is it you expect to roll back if the query fails?

Similarly, if you wish to DELETE a record - what is it you wish to roll back if the transaction fails?

TRANSACTIONS, in more simple terms, make sense with respect to INSERT and UPDATE queries - more specifically, if they're is more than one operation going on (directly or indirectly) where you truly need an all-or-nothing decision.

To delete record using transaction

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0