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?

Also i would suggest to not use MySQL user variables if you not really have to as they add unneeded extra complexity to the query. As iam pretty sure you trying to execute UPDATE tbl_A SET idaccount = 2 WHERE id IN (SELECT id FROM tbl_A WHERE FIND_IN_SET(description, 'STRING1,STRING2,STRING3,STRING4') AND fieldtype = ''); Separing the SQL query to use Divide and conquer isn't always the best approah — Raymond Nijland
– Raymond Nijland, Commented Jun 19, 2019 at 14:30
@RaymondNijland This is the minimal I can give for my problem. I can't give you real values and real field's name. — Nico
– Nico, Commented Jun 19, 2019 at 14:34
"This is the minimal I can give for my problem. I can't give you real values and real field's name" did you real the answer it's easy to provide a sample data set. It doesn't have to be real data. It just has to fairly reflect the nature of the problem at hand. — Raymond Nijland
– Raymond Nijland, Commented Jun 19, 2019 at 14:36
@RaymondNijland Your idea is incorrect because you can't update the table and use the same table in the sub-query. You will have to cheat a little as in the example of Tim Biegeleisen. — Nico
– Nico, Commented Jun 19, 2019 at 14:36
"Your idea is incorrect because you can't update the table and use the same table in the sub-query. You will have to cheat a little as in the example of Tim Biegeleisen." yes @nico i should have said UPDATE tbl_A SET idaccount = 2 WHERE id IN (SELECT id FROM (SELECT id FROM tbl_A WHERE FIND_IN_SET(description, 'STRING1,STRING2,STRING3,STRING4') AND fieldtype = '') AS a); thats why i asked example data and expected results so we don't have to geuss annymore. — Raymond Nijland
– Raymond Nijland, Commented Jun 19, 2019 at 14:38

Tim Biegeleisen · Accepted Answer · 2019-06-19 14:29:15Z

2

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.

answered Jun 19, 2019 at 14:29

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Raymond Nijland Over a year ago

Why would you need to use a MySQL user variable in the first place. it add extra unneed complexity over FIND_IN_SET(description, "STRING1,STRING2,STRING3,STRING4") also when you forgot to change the @VAR1 the next query which uses @VAR1 also will also have that old value

Tim Biegeleisen Over a year ago

@RaymondNijland I agree with all your comments, and variables should be avoided if not necessary. I only tried to answer with something working as close as possible to the OP's original query.

Raymond Nijland Over a year ago

"I only tried to answer with something working as close as possible to the OP's original query." I know as i give you a upvote as the answer is valid for what the topicstarter wants or needs, i just wanted to warn the topicstarter about possible (hard to debug) bugs which might happen.

Nico Over a year ago

@TimBiegeleisen As you said: variables should be avoided if not necessary, I will follow your advice and review my query to remove all variables.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related