存储过程查询结果如何赋值给变量？

[寂灭我有]

我需要根据子查询条件得到其结果，对A,B两张表进行更新，但是如果我先对A表(子查询也是A表)更新了，查询结果就变了，此时对B表更新条件就不正确。如下所示：

先对表A进行更新，
update A set A.Status = 2 where A.Id in
(
select id from (select t.id from A as t where t.Status = 1)as t
)

然后对表B进行更新
update B set B.Name= "test" where B.Id in
(
select id from (select t.id from A as t where t.Status = 1)as t //此时表A的Status不在是1了，状态变更了
)

是否有方法可以把select id from (select t.id from A as t where t.Status = 1)as t 这个的结果赋值到临时变量，然后在进行表A和B的操作，谢谢！

[寂灭我有]

对了，数据库是MySql

[AHUA1001]

首先，给你的两个update语句简化一下。 update A set A.Status = 2 where A.Id in (select t.id from A as t where t.Status = 1); update B set B.Name= "test" where B.Id in (select t.id from A as t where t.Status = 1); 然后，如果你select t.id from A as t where t.Status = 1的查询，结果只有1条记录，可以这样写。 SET @s ; select t.id into @s from A as t where t.Status = 1 ; update A set A.Status = 2 where A.Id in (select @s from dual); update B set B.Name= "test" where B.Id in (select @s from dual); 最后，如果select t.id from A as t where t.Status = 1的结果是多条，其实更简单。 update B set B.Name= "test" where B.Id in (select t.id from A as t where t.Status = 1); update A set A.Status = 2 where A.Id in (select t.id from A as t where t.Status = 1); 就是先更新B表，这时A表的数据不发生改变，然后再更新A表。

[Hello World,]

先对表B进行更新
update B set B.Name= "test" from B inner join A on B.Id=A.id where a.Status=1

再更新A
update A set Status = 2 where where Status = 1

[编程写手]

使用set @变量名：=值的方式设置变量值，建议使用触发器的方式简单方便