Reputation: 1609
I want to use a single query to update many records in my Postgres database using a Ruby hash or array, rather than having to iterate through each record and call a separate update.
# {:id => :color}
my_hash = {
1 => 'red',
2 => 'blue',
3 => 'green'
}
How I don't want to do it because it does three serial queries:
my_hash.each do |id, color|
MyModel.where(id: id).update_all(color: color)
end
How I do want to do it:
MyModel.connection.execute <<-SQL
UPDATE my_models
SET color=something
FROM somehow_using(my_hash)
WHERE maybe_id=something
SQL
Upvotes: 6
Views: 1618
Reputation: 51239
You can do this with the Postgres VALUES function:
UPDATE my_models
SET color = temp.color
FROM (SELECT *
FROM (VALUES (1, 'red'), (2, 'blue'), (3, 'green'))
AS t(id, color)
) AS temp
WHERE my_models.id = temp.id
This works reasonably well even with hundreds of values. To do this in Ruby from a hash, use something like:
values = my_hash.map { |id, color| "(#{id}, '#{color}')" }.join(', ')
# => (1, 'red'), (2, 'blue'), (3, 'green')
stmt = <<~SQL
UPDATE my_models
SET color = temp.color
FROM (SELECT *
FROM (VALUES #{values})
AS t(id, color)
) AS temp
WHERE my_models.id = temp.id
SQL
MyModel.connection.exec_update(stmt)
Note though that you really don't want to do this with user input unless you can sanitize it first or you like SQL injection attacks. I imagine something like this would work, although I haven't actually tried it:
values = my_hash.keys.map { |id| "(#{id}, ?)" }.join(', ')
# => (1, ?), (2, ?), (3, ?)
sql = <<~SQL
UPDATE my_models
SET color = temp.color
FROM (SELECT *
FROM (VALUES #{values})
AS t(id, color)
) AS temp
WHERE my_models.id = temp.id
SQL
stmt = ActiveRecord::Base.sanitize_sql([sql, *my_hash.values])
MyModel.connection.exec_update(stmt)
Upvotes: 0
Reputation: 4583
For readers that might encounter this question in 2020+, it looks like upsert_all
does what the OP wanted in Rails 6:
MyModel.upsert_all([{ id: 1, color: "red" }, { id: 2, color: "blue" }, { id: 3, color: "green" }])
Will generate something like
# Bulk Insert (26.3ms) INSERT INTO `my_models`(`id`,`color`)
# VALUES (1, 'red')...
# ON DUPLICATE KEY UPDATE `color`=VALUES(`color`)
Example inspired by this blog.
Upvotes: 1
Reputation: 1609
Another solution is to concatenate a series of updates into a single string and send that all at once. Downside is that it sends more data across the wire, but on the other hand PG doesn't have to deserialize and process the JSON.
ActiveRecord::Base.connection.execute(
my_hash.collect{|id,color| "UPDATE my_models SET color=#{color} WHERE id=#{id};"}.join('')
)
# And don't forget to sanitize the :id and :color values
Upvotes: 0
Reputation: 121919
You can use case
:
update my_models
set color = case id
when 1 then 'red'
when 2 then 'blue'
when 3 then 'green'
end;
or save the hash in a separate table:
create table my_hash (id int, color text);
insert into my_hash values
(1, 'red'),
(2, 'blue'),
(3, 'green');
update my_models m
set color = h.color
from my_hash h
where h.id = m.id;
One more option, if you know the way to select the hash as jsonb
:
with hash as (
select '{"1": "red", "2": "blue", "3": "green"}'::jsonb h
)
update my_models
set color = value
from hash, jsonb_each_text(h)
where key::int = id;
OP ruby-fying klin's third option:
sql = <<-SQL
with hash as (
select '#{my_hash.to_json}'::jsonb h
)
update my_models
set color = value
from hash, jsonb_each_text(h)
where key::int = id;
SQL
ActiveRecord::Base.connection.execute(sql)
Upvotes: 2