Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Friday, August 3, 2012 2:18 PM
Points: 15, Visits: 119
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: Yesterday @ 9:09 AM
Points: 12,965, Visits: 32,541
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1171346
Posted Wednesday, September 7, 2011 12:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 3, 2012 2:18 PM
Points: 15, Visits: 119
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: Yesterday @ 9:09 AM
Points: 12,965, Visits: 32,541
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1171380
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse