Update with Case When you need Where?

Question

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 register of some dependents, as far as I know, it is not necessary to put the WHERE inside the WHEN already specifies where to update the lines.

A coworker has this idea that if he runs like this it’s gonna suck and blow all the dependents away, I told him I wouldn’t.

Can anyone tell me for sure whether or not to have the WHERE?

Database: Sybase SQL Anywhere 16.

Answer 1

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.

Answer 2

At first it will have no impact, unless there are others FOFILHOS with those conditions. But if the CODI_EMP is useful as ID, do this UPDATE using the WHERE, we usually use WHEN to update more than one line and by what I’ve seen you want to reach those 3 only, so make a WHERE for each line.