Oracle: how to flash back a specific column?

Question

How do I flash back a specific column for all rows in a table?

For example, given this table:

select * from t as of scn 1201789714628;
a b 
- - 
x 1 
y 2 
z 3 
select * from t;
a b 
- - 
x 4 
y 5 
z 6 

I can flash back a column in a specific row as follows:

update t set b = (select b from t as of scn 1201789714628 where a='x') where a='x';
select * from t;
a b 
- - 
x 1 
y 5 
z 6 

but I can't figure out the syntax to set b to its previous value this for all rows.

update t t1 set b = (select b from t as of scn 1201789714628) t2 where t1.a = t2.a;
Error at Command Line:11 Column:60
SQL Error: ORA-00933: SQL command not properly ended

Answer 1

You may try this:

update t t1 
  set b = (select b from (select a, b from t as of scn 1201789714628) t2
           where t1.a = t2.a);

P.S. I'd recommend to copy your snapshot in a temporary table if you're not going to update it right now (it can dissapear very soon).