Update script to update a column on a Table

Question

I need help with an update script. I want to write an update script that will update a column called lenderName in my table called Lender.

CREATE TABLE [dbo].Lender ON [PRIMARY]

-----Insert Statement

INSERT INTO Lender(LenderCode,LenderName,CreateDate,LenderID)
VALUES ('W001','Wells Fargo Bank','2021-03-30 19:09:21.227','2'),
('W001','Wells Fargo Home Mortgage','2021-06-17 14:51:41.570','0011D00000FFF7ICAE'),
('P104','Mortgage Corporation','2021-03-30 19:09:21.227','212'),
('P104','PHH Mortgage Corporation','2021-06-17 14:51:41.570','0011D00000CMDv6PHD'),
('F145','First Bank','2021-03-30 19:09:21.227','390'),
('J346','Axia Financial','2021-03-30 19:09:21.227','320')

----After insert statement

So what i want to do is, update the LenderName column that has same LenderCode with the LenderName that has max CreateDate and also with the lenderID column that is > 10 characters( that is len(lenderID) > 10

---final Result set after the update statement

Answer 1

Hi @pizaro ,

Please check:

;WITH cte  
as(SELECT *  
,MAX([CreateDate]) OVER(PARTITION BY [LenderCode]) mm  
FROM [dbo].[Lender]  
WHERE LEN([LenderID])>10)  
  
UPDATE t  
SET t.[LenderName]=c.[LenderName]  
FROM [dbo].[Lender] t  
JOIN cte c  
ON t.[LenderCode]=c.[LenderCode]   

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.