Is it possible to separate the application of statements inside database transactions? This is a simple scenario that I encountered recently. 

I will fabricate a minimal scenario to illustrate the problem.

We have a database table with a column of IDs used as a foreign key to something (but which is not specified as a foreign key; it's being used to hold an ephemeral mapping index temporarily, in a really rather dangerous way that relies on external state). Each row has a value populated here. This column of data subsequently must be updated with a new mapping. 

An example of a valid mapping:

0 -&gt; 1
1 -&gt; 4
2 -&gt; 0


An example dataset:

2
1
0
2


Obvious desired result: 

0
4
1
0


Implementation (pseudocode):

for (mapping in map) {
    sql("update table set value = " + mapping.to + " where value = " + mapping.from + ";")
}


Pretty straightforward but almost as obvious is that this is completely wrong because the result we see is

0
4
4
0


The third row got the mapping applied twice.

I was thinking that maybe if the N (where N is the number of mappings) statements are executed in a transaction that it may solve the problem. But I'm not so sure, and I don't really see why it would. The transaction would just guarantee that either row 3 got zero or both mappings applied, but what I need is for only the one mapping. It basically seems like I need a whole new column to even accomplish such a simple operation in a straightforward way. 

How is this sort of situation dealt with usually? I feel like I'm missing something trivial.
You need something like 

UPDATE tab1 set value = 
   CASE value
     WHEN 0 THEN 1 
     WHEN 1 THEN 4
     WHEN 2 THEN 0 
   END;


If you need to treat values which are not listed in CASE in a different way, say don't change it ,  you need to add ELSE to CASE as  ypercubeᵀᴹ pointed out: 

UPDATE tab1 set value = 
       CASE value
         WHEN 0 THEN 1 
         WHEN 1 THEN 4
         WHEN 2 THEN 0 
         ELSE value
       END;


Or you can create a new table, insert  all mappings there,
and then (assuming mapping is unique)  

UPDATE  tab1 t SET value = 
 (SELECT new_val from mapping_table t2 WHERE t2.old_val = t.value)