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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply