UPDATE statement with JOIN in SQL Server Not Working as Expected

Question

I'm attempting to update the LAST_INSPECTION_FW field for all records in the VEHICLES_FW table with the last JOB_DATE_FW for records with the REASON_CODE_FW = 35. However, what's happening is that once the below code is executed, it's not taking into consideration the WHERE clause. This causes all of the records to update when it should just be updating those with the REASON_CODE_FW = 35.

Is there a way to restructure this code to get it working correctly? Please help, thanks!

UPDATE VEHICLES_FW
 SET VEHICLES_FW.LAST_INSPECTION_FW = JOB_HEADERS_FW.FIELD2MAX
FROM VEHICLES_FW
INNER JOIN (SELECT VEHICLE_ID_FW, MAX(JOB_DATE_FW) AS FIELD2MAX
FROM JOB_HEADERS_FW
GROUP BY VEHICLE_ID_FW) AS JOB_HEADERS_FW
ON VEHICLES_FW.VEHICLE_ID_FW = JOB_HEADERS_FW.VEHICLE_ID_FW
INNER JOIN JOB_DETAILS_FW
ON JOB_NUMBER_FW = JOB_NUMBER_FW
WHERE REASON_CODE_FW = '35'

HI Wayne, I suggest you start using table aliases in your queries so that when you reference a column it is clear from which table the column is from. Also, INNER JOIN JOB_DETAILS_FW ON JOB_NUMBER_FW = JOB_NUMBER_FW is probably not what you intended? — TT.
– TT., Commented Oct 27, 2020 at 15:20

David Browne - Microsoft · Accepted Answer · 2020-10-27 15:22:17Z

1

Common Table Expressions are your friend here. SQL Server's strange UPDATE ... FROM syntax is not. EG

with JOB_HEADERS_FW_BY_VEHICLE_ID as
(
  SELECT VEHICLE_ID_FW, MAX(JOB_DATE_FW) AS FIELD2MAX
  FROM JOB_HEADERS_FW
  GROUP BY VEHICLE_ID_FW
), q as
(
Select VEHICLES_FW.LAST_INSPECTION_FW, JOB_HEADERS_FW_BY_VEHICLE_ID.FIELD2MAX NEW_LAST_INSPECTION_FW
FROM VEHICLES_FW
INNER JOIN   JOB_HEADERS_FW_BY_VEHICLE_ID 
  ON VEHICLES_FW.VEHICLE_ID_FW = JOB_HEADERS_FW_BY_VEHICLE_ID.VEHICLE_ID_FW
INNER JOIN JOB_DETAILS_FW
  ON JOB_NUMBER_FW = JOB_NUMBER_FW
  WHERE REASON_CODE_FW = '35'
)
UPDATE q set LAST_INSPECTION_FW = NEW_LAST_INSPECTION_FW

answered Oct 27, 2020 at 15:22

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2020-10-27 15:21:56Z

0

I suspect this does what you want:

update v
set last_inspection_fw = (
    select max(j.job_date_fw)
    from job_headers_fw j
    inner join job_details_fw jd on jd.job_number_fw = j.job_number_fw
    where j.vehicle_id_fw = v.vehicle_id_fw and jd.reason_code_fw = 35
)
from vehicles_fw v

answered Oct 27, 2020 at 15:21

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

UPDATE statement with JOIN in SQL Server Not Working as Expected

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related