title: Update all records in a new table using ActiveRecord
body: <p>I have a table with 20,000+ rows.  I keep a record of each client's daily stats from an ad campaign in the stats column.  It would be easier to call up the data for certain requests if I had the totals for Conversions and Spend calculated already in separate columns so I've added them.</p>
  
  <p>Google_Records schema - </p>
  
  <pre><code>create_table "google_records", :force =&gt; true do |t|
   t.string   "user_id"
   t.string   "date"
   t.text     "stats"
   t.text     "account_name"
   t.datetime "created_at",                                                        :null =&gt; false
   t.datetime "updated_at",                                                        :null =&gt; false
   t.decimal  "total_cost",        :precision =&gt; 12, :scale =&gt; 2, :default =&gt; 0.0, :null =&gt; false
   t.integer  "total_conversions",                                :default =&gt; 0,   :null =&gt; false
  end
  
  add_index "google_records", ["date"], :name =&gt; "index_google_records_on_date"
  </code></pre>
  
  <p>Moving forward as records are created those columns will be populated.  However, I'm trying to figure out the best way to update the records that are already in the table.</p>
  
  <p>Stats is a hash and I can get the total for one record like so:</p>
  
  <pre><code>user = User.find(6)
  GoogleRecord.where(user_id: user).where(date: "20140328").map {|m| m.stats}.map{ |s| s.map{ |key,        value| value[:cost] || 0 } }.map {|m| m.inject(:+)}.compact.reduce(&amp;:+)
  =&gt; 660.26
  </code></pre>
  
  <p>I'm currently thinking of writing a migration that could update all the tables at once.  Is this possible?</p>
  
  <p>I'm having trouble getting the code to execute properly for even one user (although I do feel as though this may be an improper use of update_all)</p>
  
  <pre><code>("20140320".."20140323").each do |d|
    user = User.find(6)
    totalcost = GoogleRecord.where(user_id: user).where(date: d).map {|m| m.stats}.map{ |s| s.map{ |key, value| value[:cost] || 0 } }.map {|m| m.inject(:+)}.compact.reduce(&amp;:+)
    GoogleRecord.where(user_id: user).where(date: d).update_all(:total_cost =&gt; totalcost)
  end
  </code></pre>
  
  <p>Am I going in the right direction conceptually?</p>
  
  <p>Update***</p>
  
  <p>While trying to implement suggestions I realized not all users have these stats.  If I can get it working for just one user I'll be able to rewrite it for all users that have stats.  I believe I'm close to getting it to work for one user but I can't figure out where I'm messing up this each block.</p>
  
  <p>Here is what I have: </p>
  
  <pre><code>user = User.find(6)
  ("20140320".."20140323").each do |d|
  google_record = GoogleRecord.where(user_id: user).where(date: d)
  total_cost = google_record.map {|m| m.stats.map{ |key, value| value[:cost] || 0 }}.compact.reduce(&amp;:+) || 0
  google_record.update_all(:total_cost =&gt; total_cost)
  end
  </code></pre>
  
  <p>I'm now getting the following error that suggests this block is trying to insert the results of all records' <code>total_cost</code> into <code>:total_cost</code> but after rewriting this a few times I can't get it so that each separate <code>total_cost</code> calculation goes into the corresponding <code>:total_cost</code> column</p>
  
  <pre><code>←[1m←[36mGoogleRecord Load (2.0ms)←[0m  ←[1mSELECT "google_records".* FROM "google_records"  WHERE "google_records"."user_id" = 6 AND "google_records"."date" = '20140320'←[0m
  ←[1m←[35mSQL (2.0ms)←[0m  UPDATE "google_records" SET "total_cost" =  258.92,35.82,18.58,47.78,1.42,0.0,0.0,0.0,0.0,0.0,0.44,82.07,2.19,5.87,0.0,0.0 WHERE   "google_records"."user_id" = 6 AND "google_records"."date" = '20140320'
  ActiveRecord::StatementInvalid: SQLite3::SQLException: near "35.82": syntax error: UPDATE "google_records" SET "total_cost" = 258.92,35.82,18.58,47.78,1.42,0.0,0.0,0.0,0.0,0.0,0.44,82.07,2.19,5.87,0.0,0.0 WHERE "google_records"."user_id" = 6 AND "google_records"."date" = '20140320'
  </code></pre>