Alexandria Alexandria - 1 month ago 21
SQL Question

Concatenated where clause

I cant get this SQL query to update my database activties table. This is my query:-

UPDATE Activities
SET CJSID = CJSEmpNum.StaffNumber
FROM CJSEmpNum
Where Activites.Name = CJSEmpNum.Surname + " " + CJSEmpNum.Forename


I think my problem is the where clause. In activities number Name is stored as Smith John and in CJSEmpNum it is stored in two seperate columns Surname and Forename.

Answer

Have you tried making this an inner join? Something like...

UPDATE Activities SET
A.CJSID = CJSEmpNum.StaffNumber FROM CJSEmpNum INNER JOIN Activities A ON A.Name = CJSEmpNum.Surname + ' ' + CJSEmpNum.Forename