mysql - 使用mysql子查询更新错误: Subquery returns more than 1 row

Question

我正在尝试使用 Historical_state_order 表的最新字段更新订单表的“cancellation_date”字段，但它给了我一个错误“子查询返回超过 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)
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);

Answer 1

您不需要在子查询中使用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匹配的条件。