Update fullname column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Update fullname column

Update fullname column

Rate Topic

Display Mode

Topic Options

Author

Message

hardial_bhatia

Posted Wednesday, September 07, 2011 12:25 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, August 03, 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

Lowell

Posted Wednesday, September 07, 2011 12:30 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 8:39 PM
Points: 11,638, Visits: 27,713

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

hardial_bhatia

Posted Wednesday, September 07, 2011 12:34 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, August 03, 2012 2:18 PM
Points: 15, Visits: 119

But i want to update only those fields which comes in as not null

Post #1171351

Lowell

Posted Wednesday, September 07, 2011 1:18 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 8:39 PM
Points: 11,638, Visits: 27,713

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 »

Permissions