My Favorite PostgreSQL Queries and Why They Matter
&gt; 6. UPDATE multiple rows with a CASE expression<p>Oh, man, as a seasoned DBA&#x2F;Data Analyst, don&#x27;t do this unless you have a really good reason to.  This is premature optimization of the kind you want to avoid.<p>Yes, it&#x27;s really neat to update everything in a single statement, and in <i>some</i> situations it can perform significantly better, but CASE expressions in an UPDATE statement quickly get complicated to map out in your head.  It gets extremely difficult extremely quickly to tell where you have an error, and it&#x27;s very, very easy to make a very costly mistake.<p>If you really need an atomic change you can just do this:<p><pre><code>  BEGIN;
  UPDATE reward_members SET member_status = &#x27;gold_group&#x27; WHERE member_status = &#x27;gold&#x27;;
  UPDATE reward_members SET member_status = &#x27;bronze_group&#x27; WHERE member_status = &#x27;bronze&#x27;;
  UPDATE reward_members SET member_status = &#x27;platinum_group&#x27; WHERE member_status = &#x27;platinum&#x27;;
  UPDATE reward_members SET member_status = &#x27;silver_group&#x27; WHERE member_status = &#x27;silver&#x27;;
  COMMIT;
</code></pre>
In general, however, make your queries difficult for the server and easy for you, because <i>you</i> make a ton more mistakes than the server ever will.  Let the query planner and optimizer do the work.  If performance becomes a problem on this query, you can fix it later when you can focus on just that one issue and understand the specific problem much better.<p>&gt; You can imagine how many round trips this would take to the server if multiple individual UPDATE statements had been run.<p>If you&#x27;re not returning data and you reuse the connection like you&#x27;re supposed to, &quot;round trips&quot; cost is essentially nothing.  What&#x27;s expensive here is that the database server has to scan the index or row data on member_status.  However, if the table is not billions of rows, it can probably fit that index (or even the row data for small tables) in memory and will cache hit on everything.<p>However, the list of single UPDATE statement <i>can</i> perform much better than a monolithic statement.  If you&#x27;re only updating a portion of the table, or if the number of rows that you&#x27;ll actually be updating is comparatively small, then the list of single UPDATE statements can perform <i>much</i> better.  It all depends on exactly what you&#x27;re doing with the table.