This content has been marked as final. Show 3 replies
suppose i am deleting some rows from one table and those deleted rows have to insert into other empty table...how can we do that? i know this is very simple..but i am not an sql or pl/sql expert..
insert into new_table select <whatever> from old_table where <some conditions>; delete from old_table where <some conditions>;
In PLSQL, you can use RETURNING INTO clause (an extention to DML) in INSERT/UPDATE/DELETE staement to return affected rows by the statement. like. (not tested)
DECLARE l_id t1.id%TYPE; BEGIN INSERT INTO t1 VALUES (t1_seq.nextval, 'FOUR') RETURNING id INTO l_id; COMMIT; DBMS_OUTPUT.put_line('ID=' || l_id); UPDATE t1 SET description = description WHERE description = 'FOUR' RETURNING id INTO l_id; DBMS_OUTPUT.put_line('UPDATE ID=' || l_id); DELETE FROM t1 WHERE description = 'FOUR' RETURNING id INTO l_id; DBMS_OUTPUT.put_line('DELETE ID=' || l_id); COMMIT; END; Output --------------- ID=4 UPDATE ID=4 DELETE ID=4 PL/SQL procedure successfully completed.