mysql - 使用 Union 执行更新

Question

我有这份联盟声明:

(SELECT id FROM bby_venta_co WHERE nom_agrupacion = 'CAFE NESCAFE' 
AND promocion_id = 100000189 
AND ticket_id = 156 
AND fondo_fijo_id = 14 
AND caja_id = 1 ORDER BY costo DESC LIMIT 1)
UNION ALL
(SELECT id FROM bby_venta_co WHERE nom_agrupacion = 'CAFE INTERNA' 
AND promocion_id = 100000189 
AND ticket_id = 156 
AND fondo_fijo_id = 14 
AND caja_id = 1 ORDER BY costo DESC LIMIT 1);

这给我带来了两行(214,158)，我需要这些信息来执行我的更新

UPDATE bby_venta_co SET tupla_usada = 1 WHERE id in(214,158); 

但是当我尝试使用子查询时，我遇到了 UNION 单词的语法错误。

我可以将 Update 查询与 Union 语句混合使用吗？或者也许我必须使用两个查询才能获得成就？任何帮助将不胜感激。

Answer 1

UNION 不适用于子查询，但适用于查询。只需省略两个 SELECT 两边的括号即可。

您应该能够将整个查询SELECT ... UNION SELECT ...用作子查询，然后将其括在括号中。

但是order by不能用于联合的单个查询，而只能用于联合结果。因此以下内容不起作用:

UPDATE bby_venta_co SET tupla_usada = 1 WHERE id in(
    SELECT id FROM bby_venta_co WHERE nom_agrupacion = 'CAFE NESCAFE' 
    AND promocion_id = 100000189 
    AND ticket_id = 156 
    AND fondo_fijo_id = 14 
    AND caja_id = 1 ORDER BY costo DESC LIMIT 1
    UNION ALL
    SELECT id FROM bby_venta_co WHERE nom_agrupacion = 'CAFE INTERNA' 
    AND promocion_id = 100000189 
    AND ticket_id = 156 
    AND fondo_fijo_id = 14 
    AND caja_id = 1 ORDER BY costo DESC LIMIT 1
); 

由于您在这里只使用两个单个值，因此使用 OR 进行两个子选择应该可以工作，从而完全避免 UNION:

UPDATE bby_venta_co SET tupla_usada = 1 WHERE id =
    (SELECT id FROM bby_venta_co WHERE nom_agrupacion = 'CAFE NESCAFE' 
    AND promocion_id = 100000189 
    AND ticket_id = 156 
    AND fondo_fijo_id = 14 
    AND caja_id = 1 ORDER BY costo DESC LIMIT 1)
  OR id =
    (SELECT id FROM bby_venta_co WHERE nom_agrupacion = 'CAFE INTERNA' 
    AND promocion_id = 100000189 
    AND ticket_id = 156 
    AND fondo_fijo_id = 14 
    AND caja_id = 1 ORDER BY costo DESC LIMIT 1)
;