sql server - SQL query not executing on all records in temp table

Question

I have a SQL query that is supposed to update a table with records that are found in a second table. Currently, I have a temp table that it's supposed to search through and for every matching record, UPDATE the main table but the query only executed on 4 matching records when it's supposed to execute on all 23 matching records.

Is there perhaps an issue with my query? If I use an INNER JOIN or JOIN, I get the result

(4 rows affected)

If I use a LEFT JOIN, I get the error message:

Msg 515, Level 16, State 2, Line 87
Cannot insert the value NULL into column 'ChangeRequestId', table 'dbo.NSTest_RVCR'; column does not allow nulls. UPDATE fails.

Below is the UPDATE query I'm running

UPDATE c
SET c.ChangeRequestId = a.Id
FROM NSTest_RVCR c
JOIN dbo.NSChangeRequests a ON a.Id = c.ChangeRequestId
JOIN #tempExtractedTPNumber b ON b.ExtractedTP = a.TPNumber
JOIN NSReportVtest d ON d.Id = c.ReportVersionId

EDIT:

If I use a LEFT JOIN with ISNULL() as seen in the below query, I get the result: (1025 rows affected) but it still only updates the exact same 4 records.

UPDATE c
SET c.ChangeRequestId = ISNULL(a.Id,0)
FROM NSTest_RVCR c
LEFT JOIN dbo.NSChangeRequests a ON a.Id = c.ChangeRequestId
LEFT JOIN #tempExtractedTPNumber b ON b.ExtractedTP = a.TPNumber
LEFT JOIN NSReportVtest d ON d.Id = c.ReportVersionId

Original question where I had my initial issue can be found here: Update multiple records in table by using a reference table SQL Server

Answer 1

等待大神答复