Single Postgres query to update many records using a local hash/array
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)