April 25, 2011 at 9:00 pm
Hi folks....
I have a table named 'A' with 20,000 records in it. I need to delete all these 20,000 records.
Table 'A' has a column named 'ID' which is a primary key in Table 'A' and this 'ID' column is a foreign key in Tables 'B', 'C', AND 'D'.
I approached this by deleting the records in the dependency tables(B,C,D) first and then deleting in the main Table 'A'.
Delete From B where ID=A.ID
Delete From C where ID=A.ID
Delete From D where ID=A.ID
Deleting from Table A
My doubt is how can i delete all the 20,000 records at a time?? If i use above method then i have to use Delete command more than 20,000 times.
Is there any other method to do it??
Thanks in advance.....
April 25, 2011 at 10:03 pm
Delete From TableB
where exists (select * from TableA
where TableA.LinkedID=TableB.LinkedID )
Repeat this for tables B and C, and then run
Delete from TableA
If you need to delete not all records from TableA you may use following code:
Delete From TableB
where exists (select * from TableA
where {contition for the deletion} and TableA.LinkedID=TableB.LinkedID )
_____________
Code for TallyGenerator
April 26, 2011 at 3:25 pm
If you want to do this all in one command, you need to turn cascading deletes on for foreign keys. That's what it's built for.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply