选择结果集到一个变量中，然后在同一存储过程中稍后的更新中使用该变量 [英] Select result set into a variable, and then use that variable in an update later in the same stored procedure

问题描述

好的，所以这是不可能的，或者我在谷歌上很糟糕.(后者在这里更有可能)
我的问题是:使用存储过程，是否可以将 id 的结果集选择到一个变量中，然后将这些 id 用作 IN 在同一 SP 中运行的更新中的条件?我要做这样的事情(我知道我在这里忽略了分隔符冲突)

Okay, so this is either not possible, or I suck at Googling. (the latter being the more likely here)
My question is this: Using stored procedures, is it possible to select a result set of ids into a variable, and then use those ids as the condition for an IN in an update that runs in the same SP? I'm going for something like this (I recognize I'm ignoring delimiter conflicts here)

CREATE PROCEDURE test()
BEGIN
DECLARE var1 INT DEFAULT 0;
SELECT id INTO var1 FROM table WHERE this='this';
some other stuff
UPDATE table2 SET blah='blah' WHERE fk_id IN (var1);
END;

一些其他的东西"基本上以这样一种方式操作数据，我将无法通过在 IN 中使用内部查询来获得适当的 id 列表，所以我基本上需要保存" 将适当的 id 列表放入一个变量中，然后稍后使用该变量.这能做到吗?

"some other stuff" basically manipulates data in such a way that I wont be able to get the appropriate list of ids by using an inner query inside the IN, so I basically need to "save" the appropriate list of ids into a variable and then use that variable later. Can this be done?

请不要建议其他方法来执行此操作/重构我的数据/逻辑，因此我不必担心这一点.我需要知道的是是否/如何做到这一点.如果无法完成，我有备份选项，这将是最可取的方式.

Please don't suggest other ways of doing this/restructuring my data/logic so I don't have to worry about this. All I need to know is if/how this can be done. I have backup options if it can't be done, this would just be the most preferable way.

提前致谢.

推荐答案

是这样的:

CREATE PROCEDURE test()
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp AS (SELECT id FROM table WHERE this='this');
some other stuff
UPDATE table2 SET blah='blah' WHERE fk_id IN (SELECT id FROM tmp);
END;

这篇关于选择结果集到一个变量中，然后在同一存储过程中稍后的更新中使用该变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！