Update with Case when needs where?
UPDATE bethadba.FOFILHOS
SET CPF = CASE
WHEN CODI_EMP = 61 AND I_EMPREGADOS = 156 AND i_filhos = 1 THEN '00551366133'
WHEN CODI_EMP = 57 AND I_EMPREGADOS = 290 AND i_filhos = 1 THEN '00636170993'
WHEN CODI_EMP = 61 AND I_EMPREGADOS = 333 AND i_filhos = 2 THEN '01056262958'
END
COMMIT
I developed this SQL to insert some CPF numbers in the registry of some dependents, as far as I know, it is not necessary to put the WHERE because inside the When already specifies where to update the rows.
A co-worker said that if you run like this it will give bad and bug all the dependents, I told him I would not bug.
Can anyone tell me for sure whether or not to have WHERE?
Bank: Sybase SQL Anywhere 16.
2 answers
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 set of records using the expression defined in where, before proceeding with the update itself. A update without where applies the change over all records in the table.
See an example of 5000 rows in PostgreSQL working in SQL Fiddle example 1
update tabela set campo=555 where id =1000;
As 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,
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 ) and in the second case a Seq Scan (sequential search) is done.
We can also note that in the first case the query is applied over 1 record (rows=1) , while the second is over 5000 records (rows=5000).
Another factor is the danger perform an improper update.
May not be your case, but just to explain why this technique is dangerous, imagine this situation:
update tabela set campo=case when id =1000 then 555 else 0 end;
Congratulations! You updated successfully ID 1000 and freebie you won the update of the other 4999 records with zero value.
Will not have any impact at first, unless there are other fluffy with those conditions. But if CODI_EMP is used as ID, do this UPDATE Using WHERE, we usually use WHEN to update more than one line and from what I saw you want to reach those 3 only, so do a WHERE for each line.