4 ms·
I'd probably use a temp table for that, rather than a case. begin; select id, new_rating into temp_ratings values (1,25),(2,94),(3,16); update table plac
by wiredfool 9y ago
I'd probably use a temp table for that, rather than a case.
begin;
select id, new_rating into temp_ratings values (1,25),(2,94),(3,16);
update table place set rating=new_rating
from temp_ratings
where place.id=temp_ratings.id;
drop table temp_ratings;
commit;
Scales well, and almost all db engines know how to do that join. You can index the id, or use a temp table, or whatever depending on the shape and quantity of your data.
(edit -- actually finished the post)
- trinitry3 9y agoThis is really bad advice: 1) This is not really a temporary table in the traditional sense; you're creating a real table and then dropping it. This has problems with concurrency (you can't have two "real" tables with the same name), among other issues. 2) Temporary tables in Postgres suck because they bloat the catalogs. Creating temporary tables regularly is better avoided. 3) There's no real need to create a table of any kind. A regular VALUES would do. (Though you might have to encode some type information into the query.)