CODEWITHSUNDEEP
sql-server-2005
Darknight
Darknight

Reputation: 2500

Selectively Update fields

I have a stored procedure that is required to update an existing record with new data, however it must not 'over write' any existing data.

For Example we may have three fields:

 FirstName | LastName | PhoneNumber

Call the above 'TableX'.

In a very simple update this could look something like this:

 Update TableX
 set    FirstName = [TableY.FirstName]
       ,LastName = [TableY.LastName]
       ,PhoneNumber = [TableY.PhoneNumer]
 etc.....

However I require that each column Only Updates (from TableY) if it is empty.

Upvotes: 1

Views: 65

Answers (4)

onedaywhen
onedaywhen

Reputation: 57073

Because an update is logically a delete then an insert there's no harm in using the COALESCE pattern, though it is probably a good idea not check for all NULL parameter values e.g. 

UPDATE TableX
   SET FirstName = COALESCE(@FirstName, FirstName), 
       LastName = COALESCE(@LastName, LastName), 
       PhoneNumber = COALESCE(@PhoneNumber, PhoneNumer)
 WHERE COALESCE(@FirstName, @LastName, @PhoneNumber) IS NOT NULL
       AND person_X_ID = @person_X_ID;

Upvotes: 0

anishMarokey
anishMarokey

Reputation: 11397

you need to use when then to do this . 

UPDATE [AdventureWorks_DB].[dbo].[activities]
SET  [FirstName ] = case  when FirstName  is  null then 'pass First Name'
                     else  [FirstName ]
end,
LastName  = case  when LastName  is  null then 'pass Last Name'
          else  LastName 
end,
PhoneNumber = case  when PhoneNumber is  null then 'pass PhoneNumber'
        else  PhoneNumber
end

Upvotes: 1

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

Update TableX
set    FirstName = ISNULL(FirstName, [TableY.FirstName])
      ,LastName = ISNULL(LastNastName, [TableY.LastName])
      ,PhoneNumber = ISNULL(PhoneNumber, [TableY.PhoneNumer])

...

If 'empty' does not mean NULL then

Update TableX
set    FirstName = case FirstName when "" then [TableY.FirstName] else FirstName end
      ,LastName = case LastName when "" then [TableY.LastName]) else LastName end
      ,PhoneNumber = case PhoneNumber when "" then [TableY.PhoneNumer] else PhoneNumber end

Upvotes: 4

Ralf de Kleine
Ralf de Kleine

Reputation: 11756

update
    TableX
set 
FirstName = (case when FirstName = null then  @firstname else FirstName end)
where 
Id=1

Upvotes: 0

Related Questions