CODEWITHSUNDEEP
mysqlunion
Blackmore
Blackmore

Reputation: 167

Execute Update with Union

I have this Union statement:

(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);

which brings me two rows (214,158) and I need this information to execute my Update

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

But when I try to use subqueries I get a syntax error with the UNION word.

Can I mix the Update query with the Union statement? Or maybe Will I have to use two queries to get my accomplishment?. Any help will be appreciate.

Upvotes: 2

Views: 15159

Answers (1)

FrankPl
FrankPl

Reputation: 13315

UNION does not work on subqueries, but on queries. Just omit the parentheses around both SELECTs.

And you should be able to use the whole query SELECT ... UNION SELECT ... as a subquery that you then enclose in parentheses.

But order by cannot be used on the single queries of the union, but only on the unioned result. Hence the following does not work:

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
);

As you just use two single values here, two subselects with OR should work, avoiding the UNION altogether:

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)
;

Upvotes: 2

Related Questions