CODEWITHSUNDEEP
mysqlsql
Paulo Künzel
Paulo Künzel

Reputation: 849

MySql: set a variable with a list

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?

Upvotes: 18

Views: 43326

Answers (1)

Jpec07
Jpec07

Reputation: 858

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;

Upvotes: 35

Related Questions