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 the id
is equal to 1.
In terms of performance, the structure with case
is much slower than the structure with where
.
A update
will first create a set of records using the expression defined in where
, before proceeding with the update itself. A update
without where
applies the change to all table records.
See an example of 5000 lines in Postgresql running in SQL Fiddle
Example 1
update tabela set campo=555 where id =1000;
As a plan of execution 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 the case
,
Example 2
update tabela set campo=case when id =1000 then 555 end;
Implementation 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 (search using the index ) and in the second case a Seq Scan is done (sequential search).
We can also note that in the first case the query is applied over 1 record (rows=1)
, while the second is about 5000 records (rows=5000)
.
Another factor is the peril perform an improper update.
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 have successfully updated the 1000 id and as a gift you have won the upgrade from the other 4999 registrations with the zero value.
my doubt was clarified by examples 1 and 2.. I believed that would be the point, it was 100% clear now. Good answer ;)
– rLinhares