CODEWITHSUNDEEP
sqlsql-update
I'm here for Winter Hats
I'm here for Winter Hats

Reputation: 593

Update table with two conditions

I have a sql script that I want to update my table based on two conditions. If Fax is Null then I want it to be "N/A" which I have in my code already. The second condition I am not sure how to meet. The second condition is if there is a fax number to only display the last four digits of that fax.

My code so far is

Update Supp_copy
Set Fax = 'N/A'
Output
Inserted.SupplierID,
Inserted.Country,
DELETED.Fax as 'Fax before update',
INSERTED.Fax as 'Fax after update'
From Supp_copy 
Where Fax is NULL

My expected output would be 

SupplierID  Country     Fax before update   Fax after update
----------  --------    -----------------   ----------------
2           USA         NULL                N/A
3           USA         (313) 555-3349      3349
16          USA         NULL                N/A
19          USA         (617) 555-3389      3389
25          Canada      NULL                N/A
29          Canada      (514) 555-2921      2921

How can I have two update or Set statements for one update? If I cannot, how can I achieve my end result?

Upvotes: 1

Views: 68

Answers (2)

Rams
Rams

Reputation: 2169

You can just substring_index function to get last 4 digits and coalesce function for null values.

Update Supp_copy
Set Fax =coalesce(substring_index(fax,'-',-1),'N/A')

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

for Sql Server:

Using right() to get the last four characters of the fax number, within coalesce() to return 'N/A' when fax is null.

Update Supp_copy
Set Fax = coalesce(right(fax,4),'N/A')
output
Inserted.SupplierID,
Inserted.Country,
DELETED.Fax as 'Fax before update',
INSERTED.Fax as 'Fax after update'
From Supp_copy

Upvotes: 1

Related Questions