UsingCaseStatementInUpdate

[eg]

I am using SQL server 2005. I have a work table I need to update with
phone numbers and a call status based on a value from the people_phones
table. Some of the people will not have a number for each type or
docall. Are using case statements the best way to do this?

Thanks

UPDATE persons
SET per.day_phone
CASE WHEN phn.type = 'Home' then phn.phone END
SET per.cell_phone
CASE WHEN phn.type = 'Cell' then phn.phone END
SET per.cellcallstatus
CASE WHEN phn.type = 'Cell' and phn.DoCall = 1 then

'Y' ELSE
'N'
END

FROM persons per
INNER JOIN people_phones phn
ON (per.personid = phn.personid)

[Plamen Ratchev]

Yes, CASE expressions is the standard way to handle this. You can also perform the update using a CTE (it is easier to
use a SELECT with the CTE to see the what will get updated and then change to UPDATE):

WITH Phones AS (
SELECT per.day_phone, per.cell_phone, per.cellcallstatus,
CASE WHEN phn.type = 'Home' THEN phn.phone END AS day_phone_new,
CASE WHEN phn.type = 'Cell' THEN phn.phone END AS cell_phone_new,

CASE WHEN phn.type = 'Cell'

AND phn.DoCall = 1
THEN 'Y' ELSE 'N' END AS cellcallstatus_new
FROM Persons AS per
INNER JOIN People_Phones AS phn
ON per.personid = phn.personid)
UPDATE Phones
SET day_phone = day_phone_new,
cell_phone = cell_phone_new,
cellcallstatus = cellcallstatus_new;

--
Plamen Ratchev
http://www.SQLStudio.com

[Iain Sharp]

If you have one person record and many people_phones records for the
same person, this will not work (it will randomly set items to null
based on the order in which it finds things in people_phones)

I suggest something like the following

update persons
set persons.day_phone = coalesce(home.phone, persons.day_phone),
persons.cell_phone = coalesce(cell.phone, persons.cell_phone)
persons.cellcallstatus = case when cell.Docall = 1 then 'Y' else 'N'
end
from persons
left outer join people_phones home on (persons.personid =
home.personid and home.type = 'Home')
left outer join people_phones cell on (persons.personid =
cell.personid and cell.type = 'Cell')
where home.personid is not null or cell.personid is not null