Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update fullname column Expand / Collapse
Author
Message
Posted Wednesday, September 7, 2011 12:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:40 AM
Points: 15, Visits: 195
Hi
I want to update my taget fullname column which is
fullname= lastname+''+firstname+''+midlename

Input(source) fullname comes in

sysid lastname firstname middlename
1 carter john null

2 null irene null


as fullname for sysid is already present in target fullname as

1. goshi cliff charles

2. rubella derrick iyen

therefore , i want to update only the field which comes in as not null


expected result:
1. carter john charles

2. rubella irene iyen


my query is;


UPDATE A
SET
fullname= RTRIM(LTRIM(A.lastname))+', '+RTRIM(LTRIM(A.firstname))+' '+RTRIM(LTRIM(A.middlename))

FROM B INNER JOIN
ON A.sysID=B.sysID
WHERE
A.lastname IS NOT NULL OR
A.firstname IS NOT NULL OR
A.A.middlename IS NOT NULL

Plz suggest me , how to do it
I really appreacite your help
Post #1171338
Posted Wednesday, September 7, 2011 12:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 14,459, Visits: 37,984
i would simply handle each field with the ISNULL FUNCTION instead:
UPDATE A
SET fullname= RTRIM(LTRIM(ISNULL(A.lastname,'')))
+ ', '
+ RTRIM(LTRIM(ISNULL(A.firstname,'')))
+ ' '
+ RTRIM(LTRIM(ISNULL(A.middlename,'')))
FROM B INNER JOIN
ON A.sysID=B.sysID




Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1171346
Posted Wednesday, September 7, 2011 12:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:40 AM
Points: 15, Visits: 195
But i want to update only those fields which comes in as not null
Post #1171351
Posted Wednesday, September 7, 2011 1:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 14,459, Visits: 37,984
if you only want to update where those three fields are not null, then the WHERE statement would be with AND isntead of OR:
...

WHERE A.lastname IS NOT NULL
AND A.firstname IS NOT NULL
AND A.middlename IS NOT NULL





Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1171380
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse