June 22, 2018 at 11:27 am
I have the following table
fiirstname lastname SSN , DOB, orderId
AAA BBB null 01/01/1980 null
AAA BBB null 01/01/1980 1234
AAA BBB null 01/01/1980 null
LLL CCC null 01/12/1980 111
LLL CC null 01/12/1980 null
I need to update the ordereID on the same table on first last name , DOB and SSN match
Result -
fiirstname lastname SSN , DOB, orderId
AAA BBB null 01/01/1980 1234
AAA BBB null 01/01/1980 1234
AAA BBB null 01/01/1980 1234
LLL CCC null 01/12/1980 111
LLL CC null 01/12/1980 111
June 22, 2018 at 12:20 pm
Hi Guras
I think something like this will do the trick:
UPDATE t1
SET t1.OrderID = v.OrderID
FROM table_1 t1
CROSS APPLY(SELECT top 1 t2.OrderID
FROM table_1 t2
where t2.FirstName = t1.FirstName and t2.LastName = t1.LastName and (t2.SSN = t1.SSN OR (t1.SSN IS NULL AND t2.SSN IS NULL)) and t2.OrderId IS NOT NULL) v
WHERE t1.OrderID IS NULL
June 22, 2018 at 12:26 pm
Guras - Friday, June 22, 2018 11:27 AMI have the following tablefiirstname lastname SSN , DOB, orderId
AAA BBB null 01/01/1980 null
AAA BBB null 01/01/1980 1234
AAA BBB null 01/01/1980 null
LLL CCC null 01/12/1980 111
LLL CC null 01/12/1980 nullI need to update the ordereID on the same table on first last name , DOB and SSN match
Result -
fiirstname lastname SSN , DOB, orderId
AAA BBB null 01/01/1980 1234
AAA BBB null 01/01/1980 1234
AAA BBB null 01/01/1980 1234
LLL CCC null 01/12/1980 111
LLL CC null 01/12/1980 111
What if you have multiple order id for the same match(on first last name , DOB and SSN) ?
Saravanan
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy