It's not possible in a single query without additional code behind (trigger or fine-grained auditing).
Of course, you can use two statements : first one updates the row and return the rowid via the RETURNING clause, second one inserts into B from A using the rowid.
What is that scenario that prevents you to use Oracle features ?
Thanks for the suggestions. I don't have permission to create and use triggers in my schema that's why i need a solution in SQL.
Ask your administrator to grant you the required privileges. If it is a real business need then you're supposed to have sufficient privileges to fulfil the job.
Why do you think you need to do it in one single sql statement?
You could do it easily with two statements.
Just make sure the two statements run in the same transaction and use the same data.
declare v_id tableA.id%type; begin -- which row should be updated? update tableA set colB='ABC' where colC='XYZ' returning id into v_id -- remember which row was updated ; if sql%rowcount > 0 then -- Do the Insert insert into table A_History (ID, colA, colB, ColC) select id, ColA, ColB, ColC from tableA where id = v_id; end if; end; /
Using Sven's code as an example, you could save the updated row in a sql plus variable. (also untested):
SQL> var v_id number
returning id into :v_id;
insert into table A_History (ID, colA, colB, ColC)
select id, ColA, ColB, ColC
where id = :v_id;