使用不同的數據更新重複記錄

我有一個特定於遷移的問題。大型表中的數據已遷移到另一個表中，但由於某些問題（並非特定於該執行緒）導致一個目標列的數據失去。目前我正在嘗試更新新表中缺失列的數據。

問題是由於新表中的目前數據，它有重複的行，我還沒有找到一種方法來進行行的一對一映射，以便可以更新缺失列的數據。

create table oldtable
(
fileNo varchar(10),
folder varchar(10),
fileType varchar(10),
col_1 varchar(10),
col_2 varchar(10),
col_3 varchar(10),
col_4 varchar(10)
);

INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('ABC' , 'Folder1','Type1' ,'111' ,'111','111' ,null);
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('ABC' , 'Folder1','Type2' ,'111' ,'111','111',null);
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('GHI' , 'Folder3','Type3' ,'333' ,'333','333','333');
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('JKL' , 'Folder4','Type3' ,'444' ,'444','444','444');
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('MNO' , 'Folder5','Type4' ,'555' ,'555' ,'555' ,'555');
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('PQR' , 'Folder6','Type4' ,'666' ,'666' ,'666' ,'666');
INSERT INTO oldtable (fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('STU' , 'Folder7','Type5' ,'777' ,'777','777','777')

create table newtable
(
fileNo varchar(10),
folder varchar(10),
fileType varchar(10),
col_1 varchar(10),
col_2 varchar(10),
col_3 varchar(10),
col_4 varchar(10)
);

INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('ABC' , 'Folder1', null ,'111' ,'111','111' ,null);
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('ABC' , 'Folder1', null,'111' ,'111','111',null);
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('GHI' , 'Folder3', null ,'333' ,'333','333','333');
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('JKL' , 'Folder4',null ,'444' ,'444','444','444');
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('MNO' , 'Folder5',null ,'555' ,'555' ,'555' ,'555');
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('PQR' , 'Folder6',null ,'666' ,'666' ,'666' ,'666');
INSERT INTO newtable(fileNo , folder, fileType, COL_1 ,COL_2 ,COL_3 ,COL_4 ) VALUES ('STU' , 'Folder7',null ,'777' ,'777','777','777')

從舊表中選擇 *；

+--------+---------+----------+-------+-------+-------+-------+
| FILENO | FOLDER  | FILETYPE | COL_1 | COL_2 | COL_3 | COL_4 |
+--------+---------+----------+-------+-------+-------+-------+
| ABC    | Folder1 | Type1    |   111 |   111 |   111 |       |
| ABC    | Folder1 | Type2    |   111 |   111 |   111 |       |
| GHI    | Folder3 | Type3    |   333 |   333 |   333 |   333 |
| JKL    | Folder4 | Type3    |   444 |   444 |   444 |   444 |
| MNO    | Folder5 | Type4    |   555 |   555 |   555 |   555 |
| PQR    | Folder6 | Type4    |   666 |   666 |   666 |   666 |
| STU    | Folder7 | Type5    |   777 |   777 |   777 |   777 |
+--------+---------+----------+-------+-------+-------+-------+

從新表中選擇 *；

+--------+---------+----------+-------+-------+-------+-------+
| FILENO | FOLDER  | FILETYPE | COL_1 | COL_2 | COL_3 | COL_4 |
+--------+---------+----------+-------+-------+-------+-------+
| ABC    | Folder1 |          |   111 |   111 |   111 |       |
| ABC    | Folder1 |          |   111 |   111 |   111 |       |
| GHI    | Folder3 |          |   333 |   333 |   333 |   333 |
| JKL    | Folder4 |          |   444 |   444 |   444 |   444 |
| MNO    | Folder5 |          |   555 |   555 |   555 |   555 |
| PQR    | Folder6 |          |   666 |   666 |   666 |   666 |
| STU    | Folder7 |          |   777 |   777 |   777 |   777 |
+--------+---------+----------+-------+-------+-------+-------+ 

嘗試更新所有記錄的 FileType 列時 -

update newtable 
set FileType = 
(
   select FileType 
   from oldtable 
   where Fileno = newtable.fileNo 
   and folder = newtable.Folder
);

除了前兩個目前重複的記錄之外，它對於所有其他記錄都可以正常工作。它顯然不會起作用，因為它會給出 - 由於那些重複記錄，“單行子查詢返回多行”錯誤。我正在嘗試使用值“Type1”和“Type2”更新前兩行的 FileType 列。在這兩條記錄中，newtable 中的哪條記錄用“Type1”更新，哪條記錄用“Type2”更新並不重要。

請提供有關使用舊表中的特定數據更新此類重複記錄到新表中的任何想法。

我嘗試使用 Merge 但它給出了類似的錯誤：

ORA-30926: 無法在源表中獲得一組穩定的行。

不確定如何使用 rowid、rownum？

使用排名ROW_NUMBER()功能將起作用。首先為兩個表中的所有行指定行號，然後使用這些行號連接，然後更新：

with 
 oldt as
 ( select fileNo , folder, fileType,
          row_number() over (partition by fileNo, folder
                             order by fileType)
            as rn
   from oldtable
 ),

 newt as
 ( select fileNo , folder, fileType,
          row_number() over (partition by fileNo, folder
                             order by fileType)
            as rn
   from newtable
 ),

 upd as
 ( select 
       n.fileType,
       o.fileType as old_fileType
   from newt n
     join oldt o
     on  n.fileNo = o.fileNo
     and n.folder = o.folder
     and n.rn     = o.rn 
 ) 

update
   upd
set 
   fileType = old_fileType ;

SQLfiddle 似乎對 Oracle 產生了錯誤，因此它僅在 SQL Server 中進行了測試：SQLfiddle-test（但這種語法也應該對 Oracle 有效）。

---

可悲的是，在 Oracle 中測試，上述方法不起作用。我認為因為以開頭的語句WITH只能是SELECT語句。即使查詢被重新排列（我嘗試了幾次重寫），Oracle 也會拋出各種錯誤。我設法讓它工作的唯一方法是在其中添加另一列newtable並對其進行唯一約束。然後以下工作（nid是添加的主鍵列）。

在 **Oracle 的 Live SQL**站點中測試：

update 
 ( with 
     oldt as
     ( select fileNo , folder, fileType,
              row_number() over (partition by fileNo, folder
                                 order by fileType)
                as rn
       from oldtable
     ),
   newt as
     ( select fileNo , folder, nid,
              row_number() over (partition by fileNo, folder  
                                 order by fileType)
                as rn
       from newtable
     ),
   upd as
     ( select 
           n.nid,
           o.fileType as old_fileType
       from newt n
         join oldt o
         on  n.fileNo = o.fileNo
         and n.folder = o.folder
         and n.rn     = o.rn 
     ) 
   select 
       up.fileType,
       ( select upd.old_fileType
         from upd 
         where upd.nid = up.nid 
       ) as old_fileType
   from newtable up
 ) x
set fileType = old_fileType ;

引用自：https://dba.stackexchange.com/questions/127984