sgmunson (5/26/2015)

---

I suspect that the original poster meant to use FROM in place of WHERE, but given the code, I'm fairly sure that there's a much easier way to code this update. Yes, the column names are horrific, but that's not necessarily a total disaster. Take a look at the following and see if it might be a more practical option:

UPDATE CSC

SET CSC.Lightsonlongname = L.[Name (Username):]

FROM dbo.CardioStaffClinical AS CSC

INNER JOIN lightson AS L

ON CSC.Forename = SUBSTRING(SUBSTRING(L.[Name (Username):], (CHARINDEX(' , ', L.[Name (Username):], 1) + 3), 50), 1,

CHARINDEX(' ', SUBSTRING(L.[Name (Username):], (CHARINDEX(' , ', L.[Name (Username):], 1) + 3), 50), 1) - 1)

AND CSC.[Surname ] = SUBSTRING(L.[Name (Username):], 1, CHARINDEX(' , ', L.[Name (Username):], 1) - 1)