19

I've been researching about MySQL statements and similar issues for some thime now and I don't seem to be having any luck.

Can I create a variable that would store a 1 column data result to be used in multiple queries? Would it be efficient to do so or am I confused about how a DB differs from a PL?

I was thinking something like the following pseudo code

SET list = Select ID from Sales;
Update items set aValue = X where salesID in list
delete SalesMessages where salesId in list

I've got about 8 updates to do in a Store Procedure I could do the select for every case instead of creating a variable, but I doesn't feel like the best approach. Any help?

Improve this question
6
  • 3
    No. MySQL (still) does not allow storing tuples in user-defined variables. They can be store scalar values only. Commented Nov 20, 2018 at 16:49
  • You can rather use JOIN or Derived Tables instead, for merging the select query with update and delete query. Commented Nov 20, 2018 at 16:50
  • 2
    Store them in a temporary table? That's the RDBMS equivalent of a list or an array... Then it's just WHERE salesID IN (SELECT * FROM tempSalesList) or similar using EXISTS or a JOIN, etc, etc. (Note, if your list really is being populated with SELECT * FROM sales, just use the sales table. Copying every one of those values elsewhere is never going to help in any way. If you need a costly sub-set of that table though, storing them in a TempTable is fine.) Commented Nov 20, 2018 at 16:50
  • Storing in a Temp table and retrieving is as far as I went... But IMHO it feels strange to have to call the select everytime. Maybe it's just me and it doesn't make much difference for a RDBMS Commented Nov 20, 2018 at 16:54
  • What benefit do you feel you would get from storing them in somewhere else instead? They have to be stored somewhere, databases store tuples in tables... Databases are even advanced enough to have caching! So, they don't have to go to disk and just work from memory... Commented Nov 20, 2018 at 16:55

1 Answer 1

Reset to default
37

Variables in MySQL require a single, simple value, generally a string, number, or boolean. What you could do, in this case, is route your Sales IDs through GROUP_CONCAT(), which will return a comma-separated list of all Sales IDs (with some limitations - you might need to adjust some config settings if you have a lot of Sales IDs and can't filter them at all), and then do a FIND_IN_SET(), which checks for a value in a comma-separated list. Something like this would work for small sets:

SET @list = (SELECT GROUP_CONCAT(ID) FROM Sales);
UPDATE items SET aValue = X WHERE FIND_IN_SET(salesID, @list) > 0;
DELETE FROM SalesMessages WHERE FIND_IN_SET(salesId, @list) > 0;

You could also bypass the variable creation entirely through a join, which would be faster, and would get around the length limitation on GROUP_CONCAT():

UPDATE items as i INNER JOIN Sales as s ON s.ID = i.salesID SET i.aValue = X;
DELETE sm FROM SalesMessages as sm INNER JOIN Sales as s ON s.ID = sm.salesID;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

dev.mysql.com/doc/refman/8.0/en/… Just a note if you're setting the variable manually - FIND_IN_SET doesn't work correctly if the values are separated by a comma and a space. So SET @list = '1, 2, 3, 4'; wouldn't work, but SET @list = '1,2,3,4'; would work.
Just as comment for someone who gets here generally trying to do complex query or update across tables, INNER JOIN may be much, much faster, and minimal complexity option in many cases - it's certainly much, much faster joining across 3 tables than a nested query across 3 tables!
Side note in case you're using user defined variable to store IDs of something in a SQL script : you don't need strings ! That means that @SET ids = 1,2,3 SELECT * FROM table1 WHERE id IN (${ids}) would work.
For some reason running two separate queries one to collect required ids and other create query with other condition and include those ids, returns smaller result, compared to combining these both queries into single query with joins while preserving all conditions on ONs, seems to return larger set of data... Guess you can't blindly replace one query with join due to how optimiser behaves. Need to explain analyse both queries and joins side by side and see whats happening differently. The problem with joins is that it takes higher range of data and applies given condition to higher range.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.