I have a set of numbers that i need to use in 2 queries. these are part of the same oracle SQL script for a 11g server:
update table1 set some_column = 1 where user_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
update table2 set some_other_column = 17 where user_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
How do you factor out that list of numbers into a variable and use it in both update statements?
You can use collections:
declare
type t_num is table of number;
num t_num;
begin
-- fill collection from query
select rownum
bulk collect into num
from dual connect by level < 10;
-- add one value to collection
num.extend;
num(num.last) := 345;
-- using in an UPDATE statement (the same you can use in INSERT and DELETE)
forall i in num.first..num.last
update table1 set some_column = 1 where user_id = num(i);
end;
