Considering a generic table, what is the difference between
Update tabela set campo= case when id=1 then 'abc' end
and
Update set campo='abc' where id=1
?
In terms of the result of the operation, none. The two code structures will update campo when id equals 1.
In terms of performance, the structure with case is much slower than the structure with where .
A update will first create a recordset using as a criterion the expression defined in where , before proceeding with the update itself. A update without where applies the change to all records in the table.
See an example of 5000 lines in PostgreSQL running in SQL Fiddle
Example 1
update tabela set campo=555 where id =1000;
As an execution plan you can see:
QUERY PLAN
Update on tabela (cost=0.28..8.30 rows=1 width=14)
-> Index Scan using tabela_pkey on tabela (cost=0.28..8.30 rows=1 width=14)
Index Cond: (id = 1000)
Now with case ,
Example 2
update tabela set campo=case when id =1000 then 555 end;
Execution Plan:
QUERY PLAN
Update on tabela (cost=0.00..85.50 rows=5000 width=14)
-> Seq Scan on tabela (cost=0.00..85.50 rows=5000 width=14)
Note that in the first case an Index Scan was used and in the second case a Seq Scan is done.
We can also note that in the first case the query is applied on 1 register (rows=1) , while the second is on 5000 (rows=5000) registers.
Another factor is danger to perform an improper upgrade.
Maybe not your case, but just to explain why this technique is dangerous, imagine this situation:
Example 3
update tabela set campo=case when id =1000 then 555 else 0 end;
Congratulations! You successfully updated the id 1000 and toast you gained the update of the other 4999 records with the value zero.
