How to update from a correlated query with rownums?-CodePudding

There are tables FILE_HISTORY and FILES. FILES has only 1 row per file, FILE_HISTORY can have multiple rows per file, if the file was edited multiple times.

I need to revert the last change for a file - substitute file_version_id in FILES with the second largest file_version_id from FILES_HISTORY (if it exists).

Select for getting file versions for each file:

select file_version_id, 
       file_id, 
       row_number() over (partition by file_id order by file_version_id desc) as rown
from FILE_HISTORY 
where file_id member of v_affected_files;

How to update FILES from this? The idea is something like:

update FILES
set file_version_id = (2nd file_version_id for this file from the above select)
where file_version_id = (1st largest file_version_id for this file from the above select)

But writing a select exactly like this doesn't seem right.

Edit: I cannot use MERGE because FILES only has file_version_id linking it with FILE_HISTORY table. It does not have file_id column.

CodePudding user response：

For sample data:

SQL> select * from files order by file_id;

FILE_ID    FILE_VERSION_ID
---------- ---------------
A                        1
B                        3      --> should be set to "2"

SQL> select * from file_history order by file_id, file_version_id;

FILE_ID    FILE_VERSION_ID
---------- ---------------
A                        1
B                        1
B                        2   --> 2nd file version for B
B                        3

Merge, instead of update (commented line #6; I don't have v_affected_files and didn't feel like creating ones):

SQL> merge into files a
  2    using (select file_id,
  3                  file_version_id,
  4                  row_number() over (partition by file_id order by file_Version_id desc) as rown
  5           from file_history
  6           --where file_id member of v_affected_files
  7          ) b
  8    on (a.file_id = b.file_id)
  9    when matched then update set
 10      a.file_Version_id = b.file_version_id
 11      where b.rown = 2;

1 row merged.

Result:

SQL> select * from files order by file_id;

FILE_ID    FILE_VERSION_ID
---------- ---------------
A                        1
B                        2

SQL>

CodePudding user response：

If you have version_id in the files table you can simply select content from file_history where file_version_id = version_id -1, checking that we are not already at version 1, in which case there is no earlier version avaliable and we do not do the update.
You need to add your condition to say which files should be updated (v_affected_files etc.).

CREATE TABLE files (
id int, 
version_id int,
content varchar(100));

CREATE TABLE FILE_HISTORY (
file_id int,
file_version_id int,
file_content varchar(100));

insert into files values(1,3,'latest version');

insert into FILE_HISTORY values (1,3,'latest version');

insert into FILE_HISTORY values (1,2,'second version');

insert into FILE_HISTORY values (1,1,'original version');

select * from files where id = 1;

ID | VERSION_ID | CONTENT       
-: | ---------: | :-------------
 1 |          3 | latest version

update files set content =
(select file_content
 from FILE_HISTORY
 where file_id = id and 
 file_version_id = version_id - 1),
 version_id = version_id -1
 where version_id > 1;

select * from files where id = 1;

ID | VERSION_ID | CONTENT       
-: | ---------: | :-------------
 1 |          2 | second version

db<>fiddle here