CODEWITHSUNDEEP
sqlruby-on-rails-3update-all
John C. Burr
John C. Burr

Reputation: 63

Rails 3 update_all using data already contained in the table on a per-record basis

This is vaguely related to: How to Update all Dates in a Table But only if you squint real hard.

Part 1 - I know that SQL can update one column on a table based on a formula that involves other columns on that same table. And it can be done with a single, simple line of SQL as follows:

UPDATE thieving_prices SET current_price = (1 + usury_rate) * current_price;

Now all prices have just been hiked by their various associated villainous rates.

Part 2 - I also know that Rails 3 offers a wonderful bit of magic called update_all which creates an UPDATE query using a single line of SQL:

ThievingPrice.update_all(:current_price = 35000.00)

generates:

UPDATE thieving_prices SET current_price = 35000.00;

Now all of the prices are identical for all products. Very useless in this sneaky store.

Part 3 - All of these prices have their own steady rates of increase and there aught to be a way to write an elegant line of code in Rails 3 to accomplish it.

And the line:

ThievingPrice.update_all(:current_price => (1 + :usury_rate) * :current_price)

doesn't work. Nor does any syntactic variation involving parenthesis, braces or brackets--so far as my experiments have shown. Surely others have come across a need for such a construction. I don't want to fall back on the incredibly slow and resource-wasting each block:

ThievingPrice.each do |tp|
  new_price = tp.current_price * (1 + tp.usury_rate)
  tp.update_attribute(:current_price => new_price)
end

By the time that finishes my victims patrons have left the store. What are my options?

Upvotes: 5

Views: 2767

Answers (1)

ScottJShea
ScottJShea

Reputation: 7111

Try ThievingPrice.update_all("current_price = (1 + ?) * current_price" , :usury_rate). it should take the parameter and multiply the current price by that amount based on the object being updated at the moment.

Upvotes: 8

Related Questions