How many rows are returned by the SELECT version of the first update in your script?
SELECT PatientKey = P.PatientKey
, AgeKey = PM.AgeKey
, GenderKey = P.GenderKey
, GenderCode = P.GenderCode
, GPPracticeKey = P.GPPracticeKey
, GPCodeKey = P.GPCodeKey
-- , PCTKey = P.PCTKey
--, PCTCode = P.PCTCode
--Added on 11/04/2007
, GPPracticeCode = P.GPPracticeCode
--
, LocationKey = P.LocationKey
, DeprivationIMDKey = PM.DeprivationIMDKey
, DeprivationNationalKey = PM.DeprivationNationalKey
FROM dbo.LoadConsultationsOP SCOP
INNER JOIN dbo.Patient P ON SCOP.PatientId = P.PatientId
INNER JOIN dbo.PatientMonths PM ON P.PatientKey = PM.PatientKey
AND SCOP.DatePeriodKey = PM.DatePeriodKey
You might also want to change the update slightly;
UPDATE SCOP
SET PatientKey = P.PatientKey
, AgeKey = PM.AgeKey
, GenderKey = P.GenderKey
, GenderCode = P.GenderCode
, GPPracticeKey = P.GPPracticeKey
, GPCodeKey = P.GPCodeKey
-- , PCTKey = P.PCTKey
--, PCTCode = P.PCTCode
--Added on 11/04/2007
, GPPracticeCode = P.GPPracticeCode
--
, LocationKey = P.LocationKey
, DeprivationIMDKey = PM.DeprivationIMDKey
, DeprivationNationalKey = PM.DeprivationNationalKey
FROM dbo.LoadConsultationsOP SCOP
INNER JOIN dbo.Patient P ON SCOP.PatientId = P.PatientId
INNER JOIN dbo.PatientMonths PM ON P.PatientKey = PM.PatientKey
AND SCOP.DatePeriodKey = PM.DatePeriodKey
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden