I want to update a row in a table say Table A and the updated row should be inserted into another table say Table B. I need to do it in a single SQL query and i don't want to do it in PL/SQL with triggers. And i tried with MERGE statement but its working with this scenario. (Note: I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0).
Thanks in Advance.
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;