0

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?

Steffen Moritz
  • 7,277
  • 11
  • 36
  • 55
Nico
  • 85
  • 1
  • 9
  • 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 Jun 19 '19 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 Jun 19 '19 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 Jun 19 '19 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 Jun 19 '19 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 Jun 19 '19 at 14:38
  • *"You will have to cheat a little as in the example of Tim Biegeleisen."* Also i never readed Tim Biegeleisen answer as it was removed.. Mine comments and his answer where made around the same time. – Raymond Nijland Jun 19 '19 at 14:49

1 Answers1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 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 – Raymond Nijland Jun 19 '19 at 14:47
  • 1
    @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. – Tim Biegeleisen Jun 19 '19 at 14:52
  • *"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. – Raymond Nijland Jun 19 '19 at 14:55
  • @TimBiegeleisen As you said: variables should be avoided if not necessary, I will follow your advice and review my query to remove all variables. – Nico Jun 19 '19 at 15:07