UPDATE based on count

Question

I want to update the number_of_votes in the users table based on the poll_records table. In rails, it looks like

User.all.each do |user|
  user.number_of_votes = user.poll_records.where("poll_record_type = 3").size
  user.save
end

I am new to PostgreSQL. How can I update users this way?

Answer 1

Could be like this:

UPDATE users u
SET    number_of_votes = p.ct
FROM  (
   SELECT users_id, count(*) AS ct
   FROM   poll_records
   WHERE  poll_record_type = 3
   GROUP  BY users_id
   ) p
WHERE    u.users_id = p.users_id
AND      u.number_of_votes IS DISTINCT FROM p.ct;  -- to avoid empty updates

I wouldn't use "User" (reserved word) or mixed case identifiers as table name. It forces double quoting.