Handle Null values useing update statment in sql server

Question

Post reply

Handle Null values useing update statment in sql server

asrinu13

Say Hey Kid

Points: 709
More actions
August 9, 2014 at 5:33 am

#297868

Hi All, i have small doubt in sql server how to handle target table unmatched records replace with NA.please tell mehow to solve this issue in sql server.
TableA:
id | Descr |name
1 |ab |h
2 |bc |K
3 |de |m
5 | |mn
6 |jk |hb
TableB:
id | Name | Descr
1 |h |
2 |k |
4 |gj |
5 |mn |
Here i want update tableB table Descr From TableA Based on id columns
i tried like below merge TableB dest using TbleA stag on dest.id=stag.id when matched then UPDATE
SET
dest.[id]= CASE WHEN coalesce(ltrim(rtrim(stag.[id])),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.[id])) END
dest.[name]= CASE WHEN coalesce(ltrim(rtrim(stag.[name])),'') = '' THEN 'NA' ELSE ltrim(rtrim(name.[id])) END
,dest. [descr]= CASE WHEN coalesce(ltrim(rtrim(stag.[descr])),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.[descr])) END
; i got out put like below
TableB:
id | Name | Descr
1 | h | ab
2 | k | bc
4 | gi |
5 | hb |NA
But i want output like below TableB:
id | Name | Descr
1 | h | ab
2 | k | bc
4 | gi | NA
5 | hb |NA
Please tell me how to handle NA values in sql server

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

MERGE is overkill here, I'd say.

You could use something along those lines:

UPDATE TableB

SET Descr = ISNULL(TableA.Descr ,'NA'), Name = CASE WHEN TableA.id IS NULL THEN TableA.Name ELSE TableB.Name END

FROM TableB

LEFT OUTER JOIN TableA ON TableB.id = TableA.id

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Based on what you posted, give this a shot:

/*

TableA:

id | Descr |name

1 |ab |h

2 |bc |K

3 |de |m

5 | |mn

6 |jk |hb

TableB:

id | Name | Descr

1 |h |

2 |k |

4 |gj |

5 |mn |

*/

declare @TableA table(

id int,

Descr varchar(10),

name varchar(10));

declare @TableB table(

id int,

name varchar(10),

Descr varchar(10));

insert into @TableA

values (1,'ab','k'),(2,'bc','K'),(3,'de','m'),(5,null,'mn'),(6,'jk','hb');

insert into @TableB

values (1,'h',null),(2,'k',null),(4,'gj',null),(5,'mn',null);

select * from @TableA;

select * from @TableB;

update b set

Descr = isnull(a.Descr,'NA')

from

@TableB b

left outer join @TableA a

on b.id = a.id;

select * from @TableA;

select * from @TableB;