Delete multiple rows

Question

Post reply

Delete multiple rows

Billy - a cool nerd

Old Hand

Points: 337
More actions
August 5, 2010 at 9:12 am

#146497

So I'm making reports for the results of this survey at my company but somebody went in and put bad answers and another person just did a bunch of tests so the answers are things like "This is a test" and I don't want them getting in the data.
I have tables for like SurveyResponse with his name, a surveyResponseID, a ResponseID, etc. which connects to the Survey_Response table by the ResponseID. The SurveyResponse table has an AnswerID which connects to the SurveyAnswer table's ID column. SurveyAnswer has the bad answers but there are more in a Survey_SubAnswer which is indirectly connected through the Survey_Question table.
It's MS SQL Server 2005.
I know the SurveyResponseID and User Account stuff but when I delete it out I want to take down all the corresponding answers and all that with it. From what I've read it doesn't seem like there's a cascade delete for SQL server 2005. Maybe I'm wrong about that - hopefully.
Thanks guys,
Billy
😎
Billy

Viewing 15 posts - 1 through 15 (of 18 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Can you write the SQL statement that SELECTs the data that you want deleted?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Billy - a cool nerd Old Hand Points: 337 More actions · Answer 2

HMmm, I suppose, with a lot of joins... but the thing that worries me is I might not get everything that is associated. Do you think it would be best to make some sort of long delete statement?

😎
Billy

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Well, the first step is deleting data is identifying which data you want to delete. When you have to delete related data from several entities, you need to write 1 delete statement for each table and delete them out in the proper order as to not violate the referential integrity constraints.

So start by writing individual SELECT statements to identify the data that you would like to remove from each table. This sometimes requires several JOIN conditions to delete the data from the outermost child table, but it is still the right way to do it.

If you need help with this, please post the table DDL and some sample data (in the form of INSERT statements) and I can help. Please reference the link in my signature line for instructions on posting sample data/DDL.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Billy - a cool nerd Old Hand Points: 337 More actions · Answer 4

Thanks man, it's cool, I really just wanted to make sure there was no cascade or that I was missing a chance to use something cool and flashy HAHAHAHA!

But to make sure, I'd have to delete the row from the child before the row in the parent, and all in one long statement with a bunch of joins, right?

😎
Billy

John Rowan SSC Guru Points: 56440 More actions · Answer 5

There is a property that you can set up on foreign keys that will allow for cascading deletes which is off by default.

But to make sure, I'd have to delete the row from the child before the row in the parent, and all in one long statement with a bunch of joins, right?

As far as deleting them out, you'll need seperate delete statements, one for each table you want to delete from. You can't delete from multiple tables in one statement.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Billy - a cool nerd Old Hand Points: 337 More actions · Answer 6

Oh really? OK, so I can't just go "Delete * from Survey_Response joined with all the tables I know the bad responses are in."

And should I not try deleting multiple rows from each table at a time? There aren't toooo many instances of bad surveys.

I'd just screw around trying it accept I always get nervous about deleting in case I take out some collateral data or something.

😎
Billy

John Rowan SSC Guru Points: 56440 More actions · Answer 7

You should delete all rows from one table, then all rows from the next, and so forth until you've cleaned out your tables. If you are nervous, do it all inside one transaction so you can check row counts to ensure that there weren't any cascaded deletes or triggers fired.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Billy - a cool nerd Old Hand Points: 337 More actions · Answer 8

Hey thanks for the advice. I appreciate it man.

🙂

Billy

😎
Billy

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 9

Have you considered just deleting the whole "survey" and load it back?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Billy - a cool nerd Old Hand Points: 337 More actions · Answer 10

You mean throwing out all the answers and everything? They've finally decided we have enough data to make reports. Sales wants reports tomorrow.

I mean, I need everything in there minus the data from these specific instances.

😎
Billy

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 11

Billy - a cool nerd (8/5/2010)
You mean throwing out all the answers and everything?

Yes.

I assume the data comes from some place, all rows related to this "survey" are easily identifiable and there is a procedure in place to load it so... if figuring it out which rows have to be deleted is not an easy task may be it is easier - and safer - to delete the whole "survey" and start from scratch.

Knowing nothing about environment and processes in place it is hard to make a recommendation - just a different idea.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Billy - a cool nerd Old Hand Points: 337 More actions · Answer 12

it's appreciated. It's actually pretty intricate for a survey - there are 13 tables because there are surveys within the survey depending on the questions and places to upload files. It's taken about a year to get the data we have so I'm not gonna be able to start over.

😎
Billy

Billy - a cool nerd Old Hand Points: 337 More actions · Answer 13

Haven't done anything cause the dba's doing some maintenance so I have to wait a little.

Would this only delete from table1?

delete from table1 a

inner join table2 b on a.id = b.exa

inner join table3 c on b.mple = c.id

inner join table4 d on c.col = d.umns

where UserAcct like 'abcdefgh'

😎
Billy

Billy - a cool nerd Old Hand Points: 337 More actions · Answer 14

I meant to say where a.UserAccount like 'abcdefgh'

😎
Billy