# How change records w/o loading them? **URL:** \ **Category:** Rails\ **Created:** [November 24, 2007, 7:20am UTC](https://www.ruby-forum.com/t/how-change-records-w-o-loading-them/121131 "2007-11-24T07:20:37Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![S-gw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@S-gw](https://www.ruby-forum.com/u/S-gw)\ **Post date:** [November 24, 2007, 7:20am UTC](https://www.ruby-forum.com/t/how-change-records-w-o-loading-them/121131/1 "2007-11-24T07:20:37Z")
It’s not clear to me how ActiveRecord is supposed to be used to update a batch of records w/o actually loading them. IOW I want to execute an UPDATE statement something like this: UPDATE database.table SET rcrd\_status=‘N’ WHERE rcrd\_status=‘Y’ AND foreign\_id=‘abcdefg’; find\_by\_sql seems to be the only way to talk to the db directly, but this process has nothing to do with finding. Also, is there a way to retrieve db & tbl names so they don’t have to be hard coded into such a query? – gw (www.railsdev.ws) ---
**Author:** ![S-gw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@S-gw](https://www.ruby-forum.com/u/S-gw)\ **Post date:** [November 24, 2007, 7:42am UTC](https://www.ruby-forum.com/t/how-change-records-w-o-loading-them/121131/2 "2007-11-24T07:42:13Z")
Well, you need a model, but with a model derived from ActiveRecord, you can use the connection property as… MyModel.connection.execute(some\_arbitrarily\_hazardous\_sql) Good luck! ---
**Author:** ![S-gw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@S-gw](https://www.ruby-forum.com/u/S-gw)\ **Post date:** [November 24, 2007, 10:21am UTC](https://www.ruby-forum.com/t/how-change-records-w-o-loading-them/121131/3 "2007-11-24T10:21:12Z")
On 24 Nov 2007, at 06:58, Greg W. wrote: > far so good. > There are a few variants on this. First you can use > ActiveRecord::Base.connection if you don’t care to nominate a > particular model. The point however is that if your models lived in > different databases you’d need to pick the right one. The connection > adapters have several methods that execute raw sql, of which execute > is the most primitive. There’s also select\_all (and a few similiar > ones), update, and delete, which differ because they return stuff. As far as an update goes you can also do MyModel.update\_all “crd\_status=‘N’”, “rcrd\_status=‘Y’ AND foreign\_id=‘abcdefg’” Fred ---
**Author:** ![S-gw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@S-gw](https://www.ruby-forum.com/u/S-gw)\ **Post date:** [November 24, 2007, 7:59am UTC](https://www.ruby-forum.com/t/how-change-records-w-o-loading-them/121131/4 "2007-11-24T07:59:10Z")
On Nov 23, 2007, at 10:41 PM, s.ross wrote: > Well, you need a model, but with a model derived from ActiveRecord, > you can use the connection property as… > > MyModel.connection.execute(some\_arbitrarily\_hazardous\_sql) Great thanks. Been writing arbitrarily hazardous sql for years. So far so good. – gw ---
**Author:** ![S-gw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@S-gw](https://www.ruby-forum.com/u/S-gw)\ **Post date:** [November 24, 2007, 5:42pm UTC](https://www.ruby-forum.com/t/how-change-records-w-o-loading-them/121131/5 "2007-11-24T17:42:21Z")
On Nov 24, 2007, at 4:20 AM, Frederick C. wrote: > > > MyModel.connection.execute(some\_arbitrarily\_hazardous\_sql) > > > is the most primitive. There’s also select\_all (and a few similiar > > > ones), update, and delete, which differ because they return stuff. > > As far as an update goes you can also do MyModel.update\_all > “crd\_status=‘N’”, “rcrd\_status=‘Y’ AND foreign\_id=‘abcdefg’” > > Fred UPDATE mydatabase.myrecords SET rcrd\_status=‘N’ WHERE rcrd\_status=‘Y’ AND foreign\_id=‘abcdefg’; I’d recommend that you do something like: Myrecord.update\_all([‘rcrd\_status = ?’, ‘N’], [‘rcrd\_status = ? AND foreign\_id = ?’, ‘Y’, ‘abcdefg’]) Furthermore, if rcrd\_status is actually defined as a :boolean, then I’d replace ‘N’ with false and ‘Y’ with true and let the database adapter sort out how the boolean is implemented. You also don’t have to worry about the right quoting for the foreign\_id with this syntax (exactly the same as in a :conditions value on a find). -Rob Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com) removed\_email\_address@domain.invalid ---
**Author:** ![S-gw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@S-gw](https://www.ruby-forum.com/u/S-gw)\ **Post date:** [November 24, 2007, 6:41pm UTC](https://www.ruby-forum.com/t/how-change-records-w-o-loading-them/121131/6 "2007-11-24T18:41:31Z")
Afaik, update\_all doesn’t support placeholders. Did something change or did I just miss it somehow initially? I hacked together a helper that did, but update\_all would only take conditions as a string, not an array. -Bill ---
**Author:** ![S-gw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@S-gw](https://www.ruby-forum.com/u/S-gw)\ **Post date:** [November 24, 2007, 6:46pm UTC](https://www.ruby-forum.com/t/how-change-records-w-o-loading-them/121131/7 "2007-11-24T18:46:12Z")
I take that back, I just tried it and it worked fine. I must be thinking about something else. 🙂 -Bill ---
**Author:** ![S-gw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@S-gw](https://www.ruby-forum.com/u/S-gw)\ **Post date:** [November 24, 2007, 7:47pm UTC](https://www.ruby-forum.com/t/how-change-records-w-o-loading-them/121131/8 "2007-11-24T19:47:40Z")
> Ah. I guess I just didn’t see that one staring me in the face > [“rcrd\_status = :disabled”, {:disabled =\> ‘N’}], > [“rcrd\_status = :enabled AND foreign\_id = :keyval”, > {:enabled =\> ‘Y’, :keyval =\> userID}]) DOH! Nevermind – wrong field name. It works. – gw (www.railsdev.ws) ---
**Author:** ![S-gw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ed655f/32.png) [@S-gw](https://www.ruby-forum.com/u/S-gw)\ **Post date:** [November 24, 2007, 7:42pm UTC](https://www.ruby-forum.com/t/how-change-records-w-o-loading-them/121131/9 "2007-11-24T19:42:24Z")
> > UPDATE mydatabase.myrecords SET rcrd\_status=‘N’ > > WHERE rcrd\_status=‘Y’ AND foreign\_id=‘abcdefg’; > > I’d recommend that you do something like: > > Myrecord.update\_all([‘rcrd\_status = ?’, ‘N’], > [‘rcrd\_status = ? AND foreign\_id = ?’, > ‘Y’, ‘abcdefg’]) Ah. I guess I just didn’t see that one staring me in the face (update\_all, delete\_all). > (exactly the same as in a :conditions value on a find). Speaking of “exactly the same”… I wanted to use this format, but it doesn’t work. Unless I’m just not doing it correctly, I guess Rails doesn’t support this same format that is supported with find? UserPrivilege.update\_all( [“rcrd\_status = :disabled”, {:disabled =\> ‘N’}], [“rcrd\_status = :enabled AND foreign\_id = :keyval”, {:enabled =\> ‘Y’, :keyval =\> userID}]) – gw (www.railsdev.ws)