CODEWITHSUNDEEP
sql-serverif-statementsql-update
YelizavetaYR
YelizavetaYR

Reputation: 1701

MS SQL IF with a select value

How do I state - If a field in my table is NULL only then do the update.

For example:

IF customer_day_phone (from my #invoice table) where id_key = @id_key -matches my parameter - is null. Then run my update.

But i'll have multiple rows coming back and I only want to update those who are NULL. 

IF select customer_day_phone from #invoice where id_key = @id_key and  customer_day_phone is null 
BEGIN
...
END

I need the IF statement I can't simply use where day_phone is NULL because its a two part update. The first part updates the value if the field is null the second update formats the data (but I don't want it formatted if it wasn't updated only if it was).

Upvotes: 0

Views: 95

Answers (3)

M.Ali
M.Ali

Reputation: 69524

I dont see any reason why you couldn't simply do TWO PART update in a single update statement.

Simply do the following. Update to the "Formatted" value in your first update and avoid running another update statement, just to update it first and then format it. 

UPDATE #invoice
 SET columnName = 'value'
WHERE customer_day_phone IS NULL  --<-- this will only bring nulls  
 AND  id_key = @id_key

Edit

From your update statement I think it should be as simple as .....

update a 
 set a.customer_day_phone = ISNULL(b.phone,'') + ' ' + ISNULL(customer_day_phone,'') 
from #invoice a 
join T_PHONE b on a.customer_no = b.customer_no 
where b.[type] = 5
 and a.customer_day_phone IS NULL
 -- and id_key = @id_key       --<-- you had this in your first query too

Upvotes: 1

xQbert
xQbert

Reputation: 35323

Assuming you want to exclude the record update entirely...

UPDATE invoice SET customer_Day_Phone = @InputValue
WHERE customer_day_Phone is null 
  and id_key = @Id_Key

or if you need to update other values on the record but not phone..

UPDATE invoice SET customer_Day_Phone = case 
                   when customer_Day_Phone  is null then @InputValue 
                   else customer_Day_phone end,
                   field2=@field2value
WHERE id_key = @Id_Key

Upvotes: 0

The Reason
The Reason

Reputation: 7973

Let me guess maybe it something like this ? 

Update #invoice set <fields which you want to update> WHERE id_key = @id_key and  customer_day_phone is null

And at this point you dont need IF Statement

Upvotes: 0

Related Questions