September 7, 2011 at 12:25 pm
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 BINNER 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
September 7, 2011 at 12:30 pm
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
September 7, 2011 at 12:34 pm
But i want to update only those fields which comes in as not null
September 7, 2011 at 1:18 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply