你不需要使用 GROUP BY 在子查询中，因为您只希望返回单个最大值：

UPDATE orden o 
SET fecha_cancelacion =
(
    SELECT MAX(CAST(fecha AS DATETIME))
    FROM historico_estados_orden h
    WHERE
        o.id_orden = h.id_orden AND
        h.estado_origen = 'OrdenWorkflow/cancelada' AND
        h.estado_fin IN ('', 'OrdenWorkflow/cancelada') AND
        h.deleted = 0
)
WHERE
    o.status = 'OrdenWorkflow/cancelada' AND
    o.deleted = 0 AND
    o.id_orden NOT IN (3258, 3221, 3219, 2857, 2836, 2861, 2884, 2878, 3442, 2876);

还要注意的是 id_orden 不需要在子查询中再次出现，因为它已存在于外部查询中。有一个条件 id_orden 外部查询和子查询之间的匹配。

do限制1

UPDATE orden o 
SET 
    fecha_cancelacion = (SELECT             
                MAX(CAST(fecha AS DATETIME))
        FROM
            historico_estados_orden h
        WHERE
            o.id_orden = h.id_orden
                AND h.estado_origen = 'OrdenWorkflow/cancelada'
                AND h.estado_fin = ''
                OR h.estado_fin = 'OrdenWorkflow/cancelada'
                AND h.deleted = 0
                AND id_orden NOT IN (3258 , 3221, 3219, 2857, 2836, 2861, 2884, 2878, 3442, 2876)
        GROUP BY h.id_orden LIMIT 1)
WHERE
    o.status = 'OrdenWorkflow/cancelada'
        AND o.deleted = 0
        AND o.id_orden NOT IN (3258 , 3221, 3219, 2857, 2836, 2861, 2884, 2878, 3442, 2876);

如果您需要费卡的订单，也可以这样做