creating/populating tmp tble with select....

[eyetry]

Have never done this before but I need to capture a value from table 'A' that will be used to update table 'B' after I delete the table 'A' row that contains the value used in table 'B'. (how's that for a circle).

I wanted to do something like

set tmp_id = select column_6 from table_a where
column_5 = 'value1' and column_8 = 'value2';

delete from table_a where column_3 = tmp_id;

update table_b
set column_2
value = sysdate
where column_1 = tmp_id;

How do I use 'set'?

 

[taupirho]

I would do it using a temp table.


create table xyz as select column_6 from table_a where
column_5 = 'value1' and column_8 = 'value2';

That gets your values squirrelled away, now you can do your delete and update using your temp table x.


In order to understand recursion, you must first understand recursion.

 

[eyetry]

was going to do that but the user ID I have to work with doesn't have the authority.

 

[taupirho]

In that case you would need to use PL/SQL. If your tablea is reasonably small, simple cursor loop would suffice to get the data from which you could then use to do what you need.

begin
for c1_rec in (select column_6 from table_a where
column_5 = 'value1' and column_8 = 'value2')
loop
delete from table_a where column_3 = c1_rec.column_6;
update table_b
set column_2 = sysdate
where column_1 = c1_rec.column_6;

end loop;

If tablea is large then you would want be using BULK COLLECT and FORALL statements to significantly speed up your processing. You can do this for small datasets too but the coding is not as intuitive as the simple for loop shown above.



In order to understand recursion, you must first understand recursion.

 

[eyetry]

used define variable and tmp tables. Sent to BA to run in production;