5

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?

Community
  • 1
  • 1
John C. Burr
  • 63
  • 1
  • 7
  • Does `ThievingPrice.update_all("current_price = (1 + ?) * current_price" , :usury_rate)` fail? – ScottJShea Mar 01 '12 at 03:55
  • No, it tries to multiply by the string 'usury_rate' – John C. Burr Mar 01 '12 at 04:32
  • But, because it is based on data that has already been verified before being stored in the database, I can safely vary your suggestion as follows: ThievingPrice.update_all("current_price = (1 + #{:usury_rate}) * current_price) and that does work. Thank you for helping me see it in a different way. – John C. Burr Mar 01 '12 at 04:35
  • Could you put that into an answer so I can approve it? – John C. Burr Mar 01 '12 at 04:55
  • Even better, it would appear that I don't have to escape the quotes at all. Which makes sense because this would feed cleanly into SQL: ThievingPrice.update_all("current_price = (1 + usury_rate) * current_price") – John C. Burr Mar 01 '12 at 05:39

1 Answers1

8

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.

ScottJShea
  • 7,041
  • 11
  • 44
  • 67