UPDATE dex_mfgx..insp_master
  SET dex_mfgx..insp_master.costperpart = t2.sct_cst_tot
  FROM dex_mfgx..insp_master AS t1
  INNER JOIN qad_repl..sct_det_sql AS t2
  ON t1.partnum = t2.sct_part
  WHERE t1.partnum = t2.sct_part and t2.sct_cst_date = MAX(t2.sct_cst_date) ;

Msg 147, Level 15, State 1, Line 6
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.