SQL

How to Revert INSERT INTO by DELETE FROM Statements

Revert INSERT INTO

Suppose that you have created two rows by INSERT INTO statements like this:

SQL> INSERT INTO HR.EMPLOYEES (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID) VALUES (207, 'Chen', 'chen@example.com', '25-APR-19', 'SA_REP');

1 row created.

SQL> INSERT INTO HR.EMPLOYEES (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID) VALUES (208, 'Smit', 'smit@example.com', '25-APR-19', 'FI_MGR');

1 row created.

If you are pretty sure that the transaction is not committed, you can always rollback changes of Data Manipulation Language (DML) on the table any time. Otherwise, you have to find some ways to revert it. In this post, I introduce two ways to revert inserted rows by DELETE FROM statements.

Flashback Transaction Query

If those rows are newly inserted, you can use Flashback Transaction Query to retrieve the undo statements against inserted rows.

SQL> select undo_sql from flashback_transaction_query where operation = 'INSERT' and table_owner = 'HR' and table_name = 'EMPLOYEES' and start_timestamp > to_date('2019-04-25 19:03:00', 'yyyy-mm-dd hh24:mi:ss');

UNDO_SQL
--------------------------------------------------------------------------------
delete from "HR"."EMPLOYEES" where ROWID = 'AAAVTFAAFAAAADLAAK';
delete from "HR"."EMPLOYEES" where ROWID = 'AAAVTFAAFAAAADLAAJ';

Then use these statements to undo INSERT INTO operations like the followings.

SQL> delete from "HR"."EMPLOYEES" where ROWID = 'AAAVTFAAFAAAADLAAK';

1 row deleted.

SQL> delete from "HR"."EMPLOYEES" where ROWID = 'AAAVTFAAFAAAADLAAJ';

1 row deleted.

Flashback Transaction Query is good at reverting committed transactions, but sometimes, you might get nothing from it because the transaction time is beyond UNDO_RETENTION. In such moment, you need another way to do it.

Regular Expressions

Here I used Notepad++ editor as the main tool to convert INSERT INTO Statements into DELETE FROM. In the Replace Dialog, we should do it like this:

Notepad++ Replace Dialog for INSERT INTO
Notepad++ Replace Dialog for INSERT INTO

In Find what:

^INSERT INTO ([^,]+)\ \(([^,]+),\ ([^,]+),\ ([^,]+),\ ([^,]+),\ ([^,]+)\)\ VALUES\ \(([^,]+),\ ([^,]+),\ ([^,]+),\ ([^,]+),\ ([^,]+)\);$
In Replace with:
DELETE FROM $1 WHERE $2=$7 AND $3=$8 AND $4=$9 AND $5=$10 AND $6=$11;
with Regular expression mode. Then click Replace All.

In the expressions, we use an inverted pattern against dots, ([^,]+) to match table names, column names and values.

The original INSERT INTO statements:

INSERT INTO HR.EMPLOYEES (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID) VALUES (207, 'Chen', 'chen@example.com', '25-APR-19', 'SA_REP');
INSERT INTO HR.EMPLOYEES (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID) VALUES (208, 'Smit', 'smit@example.com', '25-APR-19', 'FI_MGR');

Turn into DELETE FROM like this:

DELETE FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=207 AND LAST_NAME='Chen' AND EMAIL='chen@example.com' AND HIRE_DATE='25-APR-19' AND JOB_ID='SA_REP';
DELETE FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=208 AND LAST_NAME='Smit' AND EMAIL='smit@example.com' AND HIRE_DATE='25-APR-19' AND JOB_ID='FI_MGR';

Now we can use the statements to delete rows, just don’t forget to commit your result.

Please note that, the above regular expressions of replace aim for 5 column-value pairs. If you have different number of column-value pairs, you can modify the repeated components by yourself.

Leave a Reply

Your email address will not be published. Required fields are marked *