0

I'm becoming mad trying to use an "array" of values obtained from a GROUP_CONCAT into a WHERE IN statement, when GROUP_CONCAT only takes one "id" it works ok but when it takes more it doesn't.

As it follows:

START TRANSACTION;
DECLARE @coupon_ids VARCHAR(MAX);
-- Take one or more ids
SET @coupon_ids:=(SELECT IFNULL( (SELECT GROUP_CONCAT(coupon_id) FROM some_table WHERE order_id=(SELECT entity_id FROM sales_order WHERE increment_id=310033638) GROUP BY order_id),  (SELECT coupon_id FROM some_table WHERE coupon_id=310033638)));
SELECT @coupon_ids;
INSERT INTO some_table_gift VALUES (NULL,TRIM('whatever'),'','');
SET @lastid:=LAST_INSERT_ID();
-- Here if @coupon_ids is just one id, like 123 it works, if it is a list of them like 123,234,254 it doesn't works
UPDATE some_table SET owner_id=@lastid,is_gift=1 WHERE coupon_id IN (@coupon_ids);
COMMIT;
-- Same here
SELECT coupon_id,owner_id,is_gift FROM some_table WHERE coupon_id IN (@coupon_ids);

Does anyone know how to work with this?

Thanks!

Lluís M. García
  • 1
  • 1
  • 1

1 Answers1

0

What's your filed type for coupon_id, if it is not any number type than it will not work.

One way you can add quote (single quote) for each result in GROUP_CONCAT Write

GROUP_CONCAT(coupon_id SEPARATOR '","')

remove SELECT @coupon_ids

And

in QUERY try this WHERE coupon_id IN ("@coupon_ids")

Naresh
  • 785
  • 1
  • 11
  • 23
  • GROUP_CONCAT is with that SEPARATOR by default. SELECT @coupon_ids is there just to get reporting. I've tried both '@coupon_ids' and doing a replace of , with ',' plus '@coupon_ids' and it doesn't work :( – Lluís M. García Dec 14 '10 at 13:05
  • I edit `SEPARATOR '","'` And in Where clause you need somehow add double quotes around @coupon_ids, By your programming language. than you string in IN Clause will IN ("123","234","254") – Naresh Dec 14 '10 at 13:15
  • One more helpful function is `FIND_IN_SET` Try This – Naresh Dec 14 '10 at 13:28