# Does delete\_all do one DELETE statement or several? **URL:** \ **Category:** Rails\ **Created:** [November 1, 2006, 4:46pm UTC](https://www.ruby-forum.com/t/does-delete-all-do-one-delete-statement-or-several/74200 "2006-11-01T16:46:13Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\ **Post date:** [November 1, 2006, 4:46pm UTC](https://www.ruby-forum.com/t/does-delete-all-do-one-delete-statement-or-several/74200/1 "2006-11-01T16:46:13Z")
AR 1.14.4 SQL Server 2000 (via ODBC) I have two objects TargetList and Target where TargetList has\_many Targets. Somewhere in the TargetList object, I issue the command targets.delete\_all For 213 targets, this takes quite a while. When I look in the log, I see a bunch of these statements (interestingly, I can’t find a DELETE statement written anywhere in the log). SQL (0.000000) SELECT @@ROWCOUNT AS AffectedRows Target Destroy (0.010000) WHERE UniqueID = 1634 SQL (0.000000) SELECT @@ROWCOUNT AS AffectedRows Target Destroy (0.020000) WHERE UniqueID = 1635 This looks to me to indicate that one DELETE is occurring for each target in the collection instead of one big DELETE statement. Do I have to add the foreign key condition back to target\_lists explicitly in order for one SQL statement to be issued? I want my mass deletes to be efficient, one DELETE statement vs. many. Thanks, Wes ---
**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\ **Post date:** [November 1, 2006, 5:35pm UTC](https://www.ruby-forum.com/t/does-delete-all-do-one-delete-statement-or-several/74200/2 "2006-11-01T17:35:07Z")
Target.delete\_all definitely does one single delete targets.delete\_all is different. it winds up calling delete\_records, which for has\_many\_association looks like this def delete\_records(records) if @reflection.options[:dependent] records.each { |r| r.destroy } else ids = quoted\_record\_ids(records) @reflection.klass.update\_all( “#{@reflection.primary\_key\_name} = NULL”, “#{@reflection.primary\_key\_name} = #{@owner.quoted\_id} AND #{@reflection.klass.primary\_key} IN (#{ids})” ) end end So if you’ve got :dependent set, then you’ll be destroying them one by one. Fred ---
**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\ **Post date:** [November 1, 2006, 5:39pm UTC](https://www.ruby-forum.com/t/does-delete-all-do-one-delete-statement-or-several/74200/3 "2006-11-01T17:39:30Z")
Wes G. wrote: > Do I have to add the foreign key condition back to target\_lists > explicitly in order for one SQL statement to be issued? > > I want my mass deletes to be efficient, one DELETE statement vs. many. > > Thanks, > Wes Wes, Well, I couldn’t believe your post until I sat down and tried it myself and got the same behaviour as you did which I find very surprising indeed (except I see the individual deletes in my log file). I have the same 1-n relationship as you do and called a destroy\_all() on the list only to have the log file call delete on each individual list entry???.. I don’t know how this will help you except to say that you are not crazy and that I verified this too on rails 1.1.6 with delete\_all. The foreign key constraints solution will only help if you are deleting the container itself. Very interested in seeing what I am doing wrong here… My back end is postgres 8.1 using the postgres-pr drivers ilan ---
**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\ **Post date:** [November 1, 2006, 6:14pm UTC](https://www.ruby-forum.com/t/does-delete-all-do-one-delete-statement-or-several/74200/4 "2006-11-01T18:14:56Z")
Wes G. wrote: > However, I have to ask the question: Why isn’t there a delete\_all > implemented in this way on has\_many associations? Is there some concern > around lifecycle callbacks being called or something - I’m guessing that > doesn’t happen in cases where delete\_all is called. Wes, That was my understanding as well, that delete/delete\_all() circumvents the callbacks (while destroy/destroy\_all() doesn’t) so I am confused as well as to why delete\_all() is working as you discovered. The :dependant option makes no sense to me as as well since that only matters when the container is removed and that is approximately equivalent to having the foreign key constraints as was discussed earlier. Anyways, this was an eye opener and I thank you for bringing it up. Thankfully, it will be easy to refactor my code to use the class version of delete\_all. ilan ---
**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\ **Post date:** [November 1, 2006, 5:50pm UTC](https://www.ruby-forum.com/t/does-delete-all-do-one-delete-statement-or-several/74200/5 "2006-11-01T17:50:45Z")
Ilan, Per Fred’s post above, I am calling delete\_all on a dependent child collection which is why it’s doing individual deletes. This is yet another case where I expect ActiveRecord to take care of doing something that seems intuitive but doesn’t work as I expect. I expect that since I said “delete\_all” on the specific child collection (targets), that the implicit foreign\_key condition would be supplied for me when the SQL is generated. Obviously, for now, I can do the Target.delete\_all and add the appropriate condition. However, I have to ask the question: Why isn’t there a delete\_all implemented in this way on has\_many associations? Is there some concern around lifecycle callbacks being called or something - I’m guessing that doesn’t happen in cases where delete\_all is called. I already have to ability to choose whether or not I want “destroy” behavior or a full on DELETE. So it seems like this might be a gap in association management. YATTR (Yet Another Thing To Remember) 😉 Wes