Fixing a Mistake in SQL While troubleshooting an issue in my company I updated all the files in the table instead of the specific file that needed to be tested using.
create table _file_*date*_BAK as select * from _file;
update _file set category = 'null'; 
commit;

When trying to fix it I tried to pull from my backup file I got the error
update (select a.category as OLD, b.category as NEW

 from _file a inner join _file_*date*_BAK b on a.filecod = b.filecod) t

 set t.old = t.new;

Error report -
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  &quot;cannot modify a column which maps to a non key-preserved table&quot;
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

not sure why that script didn't work.
Any help would be appreciated
The error is saying that there’s a chance that Oracle will end up updating the same row twice because there is no unique constraint on _file_*date*_BAK.filecod.
The simple solution is to create the unique index on your logical backup table:
Create unique index _file_*date*_bak_uix on _file_*date*_BAK (filecod);

And then running the update statement exactly as you wrote it already.
This answer is now version dependent though as of Oracle 19.10. If you are sufficiently patched, Oracle will allow you to execute this update statement and only fail if you have data which would cause a single row to be updated multiple times. This behaviour currently needs to be manually enabled with fix control, either at your session level or for the system (talk to your DBA):
Alter session set &quot;_fix_control&quot;='19138896:1';

I expect this will become default behaviour in a future major release.