I have the following SQL:
UPDATE TableA
SET first_name = 'AAA',
last_name = 'BBB',
address1 = '123',
address2 = 'Fake St.,',
phone = '1234567',
id = '11223344'
What should I use to only update each column if it is not null?
Add a comment
|
3 Answers
update tableA
set first_name = case when first_name is null then null else 'aaa' end,
last_name = case when last_name is null then null else 'bbb' end,
...
1 Comment
Martin Smith
Or
case when first_name is not null then 'aaa' end as there is an implicit else null for unhandled cases.Just another approach less verbose (and less readable):
UPDATE TableA
SET first_name = left( 'AAA' + first_name, 3 ) ,
last_name = left( 'BBB' + last_name, 3 ) ,
address1 = left( '123' + address1, 3 ) ,
address2 = left( 'Fake St.,' + address2, len( 'Fake St.,' ) ) ,
...
Comments
For this example use the ISNULL function:
UPDATE TableA
SET first_name = ISNULL(first_name, NULL, 'AAA'),
last_name = ISNULL(last_name, NULL, 'BBB'),
(...)
GO
