October 7, 2014 at 11:21 am
I have a table that has be updated based on another table. To accomplish this I am updating the tables as below, which is fine when it finds a match on both the tables. However, i want to ensure that when there is null on the table where it is being updated from i want to skip the update, and let the value of the table being updated as it is.
i.e.
two tables as AN and LT
AN has its structure as below
RUID Name Note
JohnID John has to be changed
SamID Sam Doesnot have a corresponding value
LT has its structure as below
ID Name UID
JohnID John JohnNewID
SamID Sam NULL
Here is what i am using for update
UPDATE AN
SET
An.RUID = LT.NUID
where LT.UID = AN.RUID
FROM LT
inner JOIN AN ON LT.UID = AN.RUID
where LT.UID = AN.RUID
GO
When we run the update the result on AN is as below
RUID Name Note
JohnID John has to be changed
NULL Sam Doesnot have a corresponding value
I want the update to leave the RUID unchanged if there is no corresponding value for SAM from where it is being updated. the value being null is unlikely but possible and i just want a fallback if and when such a Null value does occur.
thanks,
October 7, 2014 at 11:48 am
SQLTestUser (10/7/2014)
I have a table that has be updated based on another table. To accomplish this I am updating the tables as below, which is fine when it finds a match on both the tables. However, i want to ensure that when there is null on the table where it is being updated from i want to skip the update, and let the value of the table being updated as it is.i.e.
two tables as AN and LT
AN has its structure as below
RUID Name Note
JohnID John has to be changed
SamID Sam Doesnot have a corresponding value
LT has its structure as below
ID Name UID
JohnID John JohnNewID
SamID Sam NULL
Here is what i am using for update
UPDATE AN
SET
An.RUID = LT.NUID
where LT.UID = AN.RUID
FROM LT
inner JOIN AN ON LT.UID = AN.RUID
where LT.UID = AN.RUID
GO
When we run the update the result on AN is as below
RUID Name Note
JohnID John has to be changed
NULL Sam Doesnot have a corresponding value
I want the update to leave the RUID unchanged if there is no corresponding value for SAM from where it is being updated. the value being null is unlikely but possible and i just want a fallback if and when such a Null value does occur.
thanks,
Maybe something like:
UPDATE AN
SET
RUID = ISNULL(LT.NUID, AN.RUID)
FROM LT
inner JOIN AN ON LT.UID = AN.RUID;
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply