Query Optimizing Request
I am not sure your logic behind those joins but some part of the query can be optimised as; (for ex; WHEN t.total_ordered &lt; t.total_received THEN 18 of your first CASE does not make sense at all as ELSE part is also equal to18`)

EDIT: Sub query brought back in with some optimisations. Syntax looks alright now.

UPDATE c2 SET stage_id = 
         CASE 
           WHEN t.required_quantity  &lt;= t.total_received THEN 27
         --WHEN t.total_ordered &lt; t.total_received THEN 18 (NOT NEED THIS BIT)
           ELSE 18
         END


FROM component c2 INNER JOIN 
     (SELECT c.component_id,
            CASE mp.component_name_id WHEN 2 THEN l.quantity*2
                 ELSE l.quantity
            END AS required_quantity,
            SUM(received) AS total_received,
                    --(NOT NEED THIS BIT AND OTHER LIST OF COLUMNS)
                    --SUM(ordered) AS total_ordered
     FROM line l 
        LEFT JOIN component c ON c.line_id = l.line_id
        LEFT JOIN materials_progress mp ON l.job_id = mp.job_id AND 
              l.line_no = mp.line_no AND c.component_name_id = 
                                                   mp.component_name_id 
     WHERE 
            mp.job_id IS NOT NULL  
            AND (ISNULL(mp.cancelled,0) = 0) --(OR CONDITION REMOVED)
            AND (ISNULL(mp.manual_override,0) = 0) --(OR CONDITION REMOVED)
            AND c.stage_id = 18
     GROUP BY  mp.job_id,  mp.line_no,  mp.component, 
                l.quantity, mp.component_name_id, component_id) AS t

ON c2.component_id = t.component_id --(INNER JOIN)