How do I write Rails ActiveRecord update query with dynamic binding?

Question

With where we can do Item.where('stock_amount > ?', 0).

Question: Is there a way to write in ActiveRecord that will produce the following SQL?

UPDATE items SET stock_amount = stock_amount - 10 where id = 1;

I have tried

Item.update(1, {stock_amount: ['stock_amount - ?', 10]}

and it runs but does not produce the SQL I want.

I know I can achieve the same result with more lines like

item = Item.find(1)
item.stock_amount = item.stock_amount - 10
item.save

and using exec_update

ActiveRecord::Base.connection.exec_update('UPDATE items SET stock_amount = stock_amount - $1 WHERE id = $2', 'sql', [[nil, 10],[nil, 1]])

This post is the closest I get.

Marcin Kołodziej · Accepted Answer · 2018-11-12 15:22:56Z

3

Item.where(id: 1).update_all(["stock_amount = stock_amount - ?", 10])

should produce:

UPDATE "items" SET stock_amount = stock_amount - 10 WHERE "items"."id" = $1  [["id", 1]]

answered Nov 12, 2018 at 15:22

Marcin Kołodziej

5,3231 gold badge12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tun Over a year ago

With Rails 5.1.6, I get little bit different SQL that achieve the same result.

UPDATE "items" SET stock_amount = stock_amount - 10 WHERE "items"."id" IN (SELECT "items"."id" FROM "items" WHERE "items"."id" = $1 ORDER BY "items"."name" ASC)  [["id", 1]]

Collectives™ on Stack Overflow

How do I write Rails ActiveRecord update query with dynamic binding?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related