Table Recovery Question

Question

Suppose that I have a table with the following schema:

tableId
field1
field2
.....

I have two copies of my database (backup and production). On the production instance, a query was accidentally run which did the following:

Update table set field2 = null where field1 = 'x';

I am trying to undo this query based on the data stored in the backup instance (where the bad update statement was not run).

What SQL statements would I need to run on the backup db to retrieve the tableId and values of field2? How would I convert that to appropriate update statements to fix production? There could be quite a few rows impacted by the query.

I think that I could select the erased values from the backup with the following query:

Select tableId, field2 where field1 = 'x';

However, I'm at a loss about how to convert that into an easy update statement. Any insight (or better ideas) would be appreciated.

Answer 1

You'll need the data to update in the same database, so when you do that select from your backup where field1 = 'x', save that to a temporary table and copy that to your production table.

You'll also need some sort of primary key between those two tables -- if you don't have that, how will you know which field2 needs to be updated?

For instance, if your table had

field1   field2
x        5
x        9
x        null
y        5

and now has

field1   field2
x        null
x        null
x        null
y        5

and if there are other fields, how will you distinguish the proper record x/5 from x/9, and more importantly, from the field2 that was null before the update?

If you do have some sort of primary key, then you can update table1 (field2) as select field2 from backuptable where table1.field1 = backuptable