SQL & PL/SQL

Database is 10Gr2 - Had a situation yesterday where someone inadvertently changed column values on a couple of hundred thousand records with incorrect value first thing in the morning and never let me know about it until later in the day. My undo retention was not large enough to create a copy of the table as it was 7 hours back with a "insert into table_2 select * from table_1 as of timestamp..." query, so I restored the previous nights backup to another machine and recovered it to 7:00 am (just before the time he made the change), created a dblink from the production db and created a copy of the table from the restored database.

My initial thought was to just update the production table with correct values from the correct copy, using something like this:


update mnt.workorders
set approvalstat = (select b.approvalstat
from mnt.workorders a, mnt.workorders_copy b
where a.workordersoi=b.workordersoi)
where exists (select *
from mnt.workorders a, mnt.workorders_copy b
where a.workordersoi=b.workordersoi)

That wasn't the exact syntax, but you get the idea, I wanted to set the incorrect values in x columns in the production tables with the correct values from the copy of the table from the restored backup. Anyway, it was (or seemed to be) working but watching the process through OEM it was estimating 100+ hours with the full table scans, so I killed it. I ended up just inserting (into the copy) the rows added to the production table since the the copy by doing a select from the production table where <col_with_datestamp> was >= 7:00:00, truncating the production table, then re inserting the rows from the now correct copy.

Doing a post mortem today, I am replaying the scenario on the copy I restored, trying to figure out a cleaner, faster way to do it, should the need arise again. I went and randomly changed some values in a number column (called "comappstat") in a copy of the production table, then thought I would try the following to reset those values from the correct table:

update (select a.comappstat, b.comappstat
from mnt.workorders a, mnt.workorders_copy b
where a.workordersoi = b.workordersoi -- this is a PK column
and a.comappstat != b.comappstat)
set b.comappstat = a.comappstat

Although I thought the syntax was correct, I get a "ORA-00904: "A"."COMAPPSTAT": invalid identifier" from running this, I was trying to figure out where the syntax was wrong here, then thought that maybe having the subquery return a single row would be cleaner and faster anyway, so I gave up on that and instead tried this:

update mnt.workorders_copy
set comappstat = (select distinct
a.comappstat
from mnt.workorders a, mnt.workorders_copy b
where a.workordersoi = b.workordersoi
and a.comappstat != b.comappstat)
where a.comappstat != b.comappstat
and a.workordersoi = b.workordersoi

The subquery run on its own returns a single value 9, which is the correct value of the column in the prod table, and I want it to replace the incorrect one "12" (I updated the copy to change the comappstat column value to 12 wherever it was 9) However when I run the query I again get this error:

ERROR at line 8:
ORA-00904: "B"."WORKORDERSOI": invalid identifier

First off, I can't see why the update statement is not working (it's probably obvious, but I can't see it)

Secondly, is this the best approach to update a column (or columns) that are incorrect, with columns from an identical table that are correct, or is there a better way?

I would sooner update the table rather than delete or truncate then re insert, as there was an insert/update trigger I had to disable on the re insert, and truncating the table rendered a function of the application unusable while I was re inserting.

thanks