MySQL: Update table with an IN (@variable) failed

Question

I'm trying to update a table, with the IN function and a variable.

The content of the variable is a sub-query that returns the expected values aka ID for each STRING. When I copy/paste the values in the update, everything is fine.

USE `DB1`;

SET SQL_SAFE_UPDATES = 0;

SET @VAR1 = "STRING1,STRING2,STRING3,STRING4";
SET @VAR2 = (SELECT GROUP_CONCAT(`id`) FROM `tbl_A` WHERE FIND_IN_SET(`description`, @VAR1) AND `fieldtype` = '');

UPDATE `tbl_A`
SET `idaccount` = 2
WHERE `id` IN (@VAR2);

SET SQL_SAFE_UPDATES = 1;

So why when I use the variable, it updates only the first row?

Answer 1

The variable @VAR2 is a scalar variable, and can't store a result set. To do that, you would need a table variable, which MySQL does not directly support (note that other databases, such as SQL Server, do support them).

But, you may inline the subquery and it should work:

SET @VAR1 = "STRING1,STRING2,STRING3,STRING4";

UPDATE tbl_A
SET idaccount = 2
WHERE id IN (SELECT id FROM (
                 SELECT id FROM tbl_A
                 WHERE FIND_IN_SET(description, @VAR1) AND fieldtype = ''
             )x );

Note that the subquery following the IN clause of the update is wrapped twice, to avoid the problem of trying to reference the same table which is being updated. This trick actually materializes the subquery, leaving it independent of what happens during the update.