0

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).

Improve this question
2
  • add it to the where clause and customer_day_phone is null Commented May 18, 2015 at 16:02
  • 1
    You don't need to test anything. Just do the update with the where clause and customer_day_phone is null. Commented May 18, 2015 at 16:04

3 Answers 3

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I'm updating the same field twice. If customer_day_phone is null, update it to set it to a value (from a join on another table). After that is done format that value to look like a phone #. But I don't want the data touched/formatted unless it was updated. Because the original data comes in accurately. This is where i'm stuck
Well that's the point, why update it first and then update it with a formatted value, why not just simply update the NULL values to a Formatted value? You still will be only updating the null values.
That's fair. But I don't know how to do that because I have a long string coming in from another table and I have to format it to look like a phone number. And this is what I had but couldn't get it to work. That is why I thought to do it in two statements. SUBSTRING(customer_day_phone, 1, 3) + '-' + SUBSTRING(customer_day_phone, 4, 3) + '-' + SUBSTRING(customer_day_phone, 7, 4)
Yes this is fine mate, just put this whole expression in SET Column = <expression> clause and it should work just fine.
Maybe you can help me right now my code looks like this update a set 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 but what I need to do is take the long strings of code and format them to look like (xxx) xxx-xxxx xxx or at least xxx-xxx-xxxx xxx etc. The previosly mentioned code is what I have and it didn't seem to work together with this.
|
0

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

Improve this answer

1 Comment

I do need the invoice statement because there is a two part update. The first update the value and the second formats it and I don't want to update (override) which your statement will be good with but I also don't want to format it if it was not updated.
0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.