DELETING AN ENTRY

Question

DELETING AN ENTRY

david.foli7

SSC-Addicted

Points: 401
More actions
April 14, 2015 at 10:06 am

#306972

I got the below request to delete an entry...what will be the best script to use
Auxid: 1219947569
Name: WALMART T
CID: 11987968

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

david.foli7 (4/14/2015)
I got the below request to delete an entry...what will be the best script to use
Auxid: 1219947569
Name: WALMART T
CID: 11987968

Hi and welcome to the forums. You need to provide some details before anybody can do much to help you here.

I suspect you want a delete statement but with nothing but a few values we can't help you much. Here is a complete shot in the dark. Make sure you understand this before you run it on your system. I am not going to be able to help you if this isn't right and you delete a row you shouldn't.

Delete from SomeTable

where Auxid = 1219947569

AND Name = 'WALMART T'

and CID = 11987968

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 2

Please also wrap this in a transaction if you're not sure.

select *

from SomeTable

where Auxid = 1219947569

AND Name = 'WALMART T'

and CID = 11987968

begin transaction

Delete from SomeTable

where Auxid = 1219947569

AND Name = 'WALMART T'

and CID = 11987968

select *

from SomeTable

where Auxid = 1219947569

AND Name = 'WALMART T'

and CID = 11987968

rollback

If you get one row from the first query and none from the second, this may be what you want. If that's the case, then you can change the "rollback" to "commit" or just execute the delete statement.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 3

To add to what Sean posted, you should never do "just" a DELETE because you don't know what to expect. You should always do a SELECT first to see how many rows will be affected and then encapsulate the DELETE in a transaction. Something like this...

First, run the SELECT statement separately to make sure that the rows contain what you think they do and to check the rowcount to see if it's as expected...

SELECT *

FROM dbo.SomeTable

WHERE Auxid = 1219947569

AND Name = 'WALMART T'

AND CID = 11987968

;

Once you've noted the rowcount, etc, then you can run the following to protect you from possible SQL Server errors. We had such an error occur back in SQL Server 2000 where the WHERE clause was totally ignored and it wiped out the whole table. Don't forget to change the "X". And don't use a VARIABLE that's populated from the SELECT for "X" because it can make the same mistake as the DELETE.

BEGIN TRANSACTION

DELETE FROM dbo.SomeTable

WHERE Auxid = 1219947569

AND Name = 'WALMART T'

AND CID = 11987968

;

IF @@ROWCOUNT <> X --Replace the "x" with the rowcount from the SELECT

BEGIN

ROLLBACK

RAISERROR('INCORRECT ROWCOUNT. ROLLED BACK.',16,1);

END

ELSE COMMIT

;

--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)