Oracle使用迭代器更新数据

我在更新一些记录时犯了一个错误，需要一些帮助来"恢复"它们。对于以下数据，我需要EC和INVOICE_NUM关键字具有迭代器后缀，如下所示。

如何迭代所有记录，并为EC和INVOICE_NUM关键字的值>1的每个RID执行此操作？如果只有一个EC或INVOICE_NUM，则不需要更新，可以保留为EC或INVOICE_NUM。

生产中：

RID       KEYWORD        VALUE
692307    BANK_NUM       17                         
692307    CHECK_AMT      $ 2504.62                  
692307    CHECK_DT       71121                   
692307    CHECK_NUM      27034                      
692307    DOC_NAME       Expense-Check              
692307    EC             71103CTRC               
692307    EC             71027TNRF               
692307    EC             71114TXWCS              
692307    EC             71117TXCP               
692307    EC             7111ONJAD               
692307    FILENAME       Q:teleform0028076     
692307    INVOICE_NUM    2193000086                 
692307    INVOICE_NUM    9190000875                 
692307    INVOICE_NUM    9600010418                 
692307    INVOICE_NUM    9600010414                 
692307    INVOICE_NUM    8100000372                 
692307    VEND_NAME      KINKO'S INC.               
692307    VEND_NUM       1002838

期望结果：

RID       KEYWORD        VALUE
692307    BANK_NUM       17                    
692307    CHECK_AMT      $ 2504.62             
692307    CHECK_DT       71121              
692307    CHECK_NUM      27034                 
692307    DOC_NAME       Expense-Check         
692307    EC_1           71103CTRC          
692307    EC_2           71027TNRF          
692307    EC_3           71114TXWCS         
692307    EC_4           71117TXCP          
692307    EC_5           7111ONJAD          
692307    FILENAME       Q:teleform0028076
692307    INVOICE_NUM_1  9190000875            
692307    INVOICE_NUM_2  2193000086            
692307    INVOICE_NUM_3  9600010418            
692307    INVOICE_NUM_4  9600010414            
692307    INVOICE_NUM_5  8100000372            
692307    VEND_NAME      KINKO'S INC.          
692307    VEND_NUM       1002838

同上的查询结果：

RID         ORIGINAL_KEYWORD    VALUE           NEW_KEYWORD
3361978    DOC_NAME             Expense-Check    DOC_NAME_1
3361979    DOC_NAME             Expense-Check    DOC_NAME_2
3361980    DOC_NAME             Expense-Check    DOC_NAME_3
3361981    DOC_NAME             Expense-Check    DOC_NAME_4
3361982    DOC_NAME             Expense-Check    DOC_NAME_5
3361983    DOC_NAME             Expense-Check    DOC_NAME_6
3361984    DOC_NAME             Expense-Check    DOC_NAME_7
3361985    DOC_NAME             Expense-Check    DOC_NAME_8
3361986    DOC_NAME             Expense-Check    DOC_NAME_9
3361987    DOC_NAME             Expense-Check    DOC_NAME_10

*更多更新*

RID        KEYWORD          VALUE
5716220    BANK_NUMBER      17                    
5716220    CHECK_AMOUNT     $ 3797.68             
5716220    CHECK_DATE       141211                
5716220    CHECK_NUMBER     67714                 
5716220    DOC_NAME         Expense-Check         
5716220    EVENT_CODE       141107NVCC            
5716220    EVENT_CODE       141107NVCC            
5716220    EVENT_CODE       141113FLRC            
5716220    EVENT_CODE       141031INRA            
5716220    EVENT_CODE       141107MSAM            
5716220    EVENT_CODE       141113SCRCO           
5716220    FILENAME         Q:teleform0133619
5716220    INVOICE_NUMBER   DB791094              
5716220    INVOICE_NUMBER   791094                
5716220    INVOICE_NUMBER   792456                
5716220    INVOICE_NUMBER   789983                
5716220    INVOICE_NUMBER   791910                
5716220    INVOICE_NUMBER   792452                
5716220    VENDOR_NAME      VTECH                 
5716220    VENDOR_NUMBER    1001685               
5716221    BANK_NUMBER      17                    
5716221    CHECK_AMOUNT     $ 3797.68             
5716221    CHECK_DATE       141211                
5716221    CHECK_NUMBER     67714                 
5716221    DOC_NAME         Expense-Check         
5716221    EVENT_CODE       141113SCRCO           
5716221    EVENT_CODE       141113AZRAR           
5716221    EVENT_CODE       141104MORER           
5716221    FILENAME         Q:teleform01336191
5716221    INVOICE_NUMBER   792959                
5716221    INVOICE_NUMBER   792508                
5716221    INVOICE_NUMBER   790437                
5716221    VENDOR_NAME      VTECH                 
5716221    VENDOR_NUMBER    1001685               
5716222    BANK_NUMBER      17                    
5716222    CHECK_AMOUNT     $ 7782.73             
5716222    CHECK_DATE       141211                
5716222    CHECK_NUMBER     67711                 
5716222    DOC_NAME         Expense-Check         
5716222    EVENT_CODE                             
5716222    EVENT_CODE                             
5716222    FILENAME         Q:teleform0133616
5716222    INVOICE_NUMBER   150562                
5716222    INVOICE_NUMBER   150533                
5716222    VENDOR_NAME      TGRAPH
5716222    VENDOR_NUMBER    1001672

结果应该是这样的：

RID        KEYWORD          VALUE
5716220    BANK_NUMBER      17                    
5716220    CHECK_AMOUNT     $ 3797.68             
5716220    CHECK_DATE       141211                
5716220    CHECK_NUMBER     67714                 
5716220    DOC_NAME         Expense-Check         
5716220    EVENT_CODE_1     141107NVCC            
5716220    EVENT_CODE_2     141107NVCC            
5716220    EVENT_CODE_3     141113FLRC            
5716220    EVENT_CODE_4     141031INRA            
5716220    EVENT_CODE_5     141107MSAM            
5716220    EVENT_CODE_6     141113SCRCO           
5716220    FILENAME         Q:teleform0133619
5716220    INVOICE_NUMBER_1 DB791094              
5716220    INVOICE_NUMBER_2 791094                
5716220    INVOICE_NUMBER_3 792456                
5716220    INVOICE_NUMBER_4 789983                
5716220    INVOICE_NUMBER_5 791910                
5716220    INVOICE_NUMBER_6 792452                
5716220    VENDOR_NAME      VTECH                 
5716220    VENDOR_NUMBER    1001685               
5716221    BANK_NUMBER      17                    
5716221    CHECK_AMOUNT     $ 3797.68             
5716221    CHECK_DATE       141211                
5716221    CHECK_NUMBER     67714                 
5716221    DOC_NAME         Expense-Check         
5716221    EVENT_CODE_1     141113SCRCO           
5716221    EVENT_CODE_2     141113AZRAR           
5716221    EVENT_CODE_3     141104MORER           
5716221    FILENAME         Q:teleform01336191
5716221    INVOICE_NUMBER_1 792959                
5716221    INVOICE_NUMBER_2 792508                
5716221    INVOICE_NUMBER_3 790437                
5716221    VENDOR_NAME      VTECH                 
5716221    VENDOR_NUMBER    1001685               
5716222    BANK_NUMBER      17                    
5716222    CHECK_AMOUNT     $ 7782.73             
5716222    CHECK_DATE       141211                
5716222    CHECK_NUMBER     67711                 
5716222    DOC_NAME         Expense-Check         
5716222    EVENT_CODE_1                             
5716222    EVENT_CODE_2                             
5716222    FILENAME         Q:teleform0133616
5716222    INVOICE_NUMBER_1 150562                
5716222    INVOICE_NUMBER_2 150533                
5716222    VENDOR_NAME      TGRAPH
5716222    VENDOR_NUMBER    1001672

该表的结构如上所示。数据来自需要更新的同一个表。

试试这样的东西：

with w_data as (
        select 5716220 rid, rtrim('BANK_NUMBER   ') keyword, rtrim('17                    ') value from dual union all
        select 5716220 rid, rtrim('CHECK_AMOUNT  ') keyword, rtrim('$ 3797.68             ') value from dual union all
        select 5716220 rid, rtrim('CHECK_DATE    ') keyword, rtrim('141211                ') value from dual union all
        select 5716220 rid, rtrim('CHECK_NUMBER  ') keyword, rtrim('67714                 ') value from dual union all
        select 5716220 rid, rtrim('DOC_NAME      ') keyword, rtrim('Expense-Check         ') value from dual union all
        select 5716220 rid, rtrim('EVENT_CODE    ') keyword, rtrim('141107NVCC            ') value from dual union all
        select 5716220 rid, rtrim('EVENT_CODE    ') keyword, rtrim('141107NVCC            ') value from dual union all
        select 5716220 rid, rtrim('EVENT_CODE    ') keyword, rtrim('141113FLRC            ') value from dual union all
        select 5716220 rid, rtrim('EVENT_CODE    ') keyword, rtrim('141031INRA            ') value from dual union all
        select 5716220 rid, rtrim('EVENT_CODE    ') keyword, rtrim('141107MSAM            ') value from dual union all
        select 5716220 rid, rtrim('EVENT_CODE    ') keyword, rtrim('141113SCRCO           ') value from dual union all
        select 5716220 rid, rtrim('FILENAME      ') keyword, rtrim('Q:teleform0133619') value from dual union all
        select 5716220 rid, rtrim('INVOICE_NUMBER') keyword, rtrim('DB791094              ') value from dual union all
        select 5716220 rid, rtrim('INVOICE_NUMBER') keyword, rtrim('791094                ') value from dual union all
        select 5716220 rid, rtrim('INVOICE_NUMBER') keyword, rtrim('792456                ') value from dual union all
        select 5716220 rid, rtrim('INVOICE_NUMBER') keyword, rtrim('789983                ') value from dual union all
        select 5716220 rid, rtrim('INVOICE_NUMBER') keyword, rtrim('791910                ') value from dual union all
        select 5716220 rid, rtrim('INVOICE_NUMBER') keyword, rtrim('792452                ') value from dual union all
        select 5716220 rid, rtrim('VENDOR_NAME   ') keyword, rtrim('VTECH                 ') value from dual union all
        select 5716220 rid, rtrim('VENDOR_NUMBER ') keyword, rtrim('1001685               ') value from dual union all
        select 5716221 rid, rtrim('BANK_NUMBER   ') keyword, rtrim('17                    ') value from dual union all
        select 5716221 rid, rtrim('CHECK_AMOUNT  ') keyword, rtrim('$ 3797.68             ') value from dual union all
        select 5716221 rid, rtrim('CHECK_DATE    ') keyword, rtrim('141211                ') value from dual union all
        select 5716221 rid, rtrim('CHECK_NUMBER  ') keyword, rtrim('67714                 ') value from dual union all
        select 5716221 rid, rtrim('DOC_NAME      ') keyword, rtrim('Expense-Check         ') value from dual union all
        select 5716221 rid, rtrim('EVENT_CODE    ') keyword, rtrim('141113SCRCO           ') value from dual union all
        select 5716221 rid, rtrim('EVENT_CODE    ') keyword, rtrim('141113AZRAR           ') value from dual union all
        select 5716221 rid, rtrim('EVENT_CODE    ') keyword, rtrim('141104MORER           ') value from dual union all
        select 5716221 rid, rtrim('FILENAME      ') keyword, rtrim('Q:teleform01336191') value from dual union all
        select 5716221 rid, rtrim('INVOICE_NUMBER') keyword, rtrim('792959                ') value from dual union all
        select 5716221 rid, rtrim('INVOICE_NUMBER') keyword, rtrim('792508                ') value from dual union all
        select 5716221 rid, rtrim('INVOICE_NUMBER') keyword, rtrim('790437                ') value from dual union all
        select 5716221 rid, rtrim('VENDOR_NAME   ') keyword, rtrim('VTECH                 ') value from dual union all
        select 5716221 rid, rtrim('VENDOR_NUMBER ') keyword, rtrim('1001685               ') value from dual union all
        select 5716222 rid, rtrim('BANK_NUMBER   ') keyword, rtrim('17                    ') value from dual union all
        select 5716222 rid, rtrim('CHECK_AMOUNT  ') keyword, rtrim('$ 7782.73             ') value from dual union all
        select 5716222 rid, rtrim('CHECK_DATE    ') keyword, rtrim('141211                ') value from dual union all
        select 5716222 rid, rtrim('CHECK_NUMBER  ') keyword, rtrim('67711                 ') value from dual union all
        select 5716222 rid, rtrim('DOC_NAME      ') keyword, rtrim('Expense-Check         ') value from dual union all
        select 5716222 rid, rtrim('EVENT_CODE    ') keyword, rtrim('                      ') value from dual union all
        select 5716222 rid, rtrim('EVENT_CODE    ') keyword, rtrim('                      ') value from dual union all
        select 5716222 rid, rtrim('FILENAME      ') keyword, rtrim('Q:teleform0133616') value from dual union all
        select 5716222 rid, rtrim('INVOICE_NUMBER') keyword, rtrim('150562                ') value from dual union all
        select 5716222 rid, rtrim('INVOICE_NUMBER') keyword, rtrim('150533                ') value from dual union all
        select 5716222 rid, rtrim('VENDOR_NAME   ') keyword, rtrim('TGRAPH                ') value from dual union all
        select 5716222 rid, rtrim('VENDOR_NUMBER ') keyword, rtrim('1001672               ') value from dual
     )
 select rid,
        CASE WHEN keyword in ( 'EVENT_CODE', 'INVOICE_NUMBER' ) 
           THEN 
              keyword || '_' || row_number() over (partition by rid, keyword order by value )
           ELSE
              keyword
        END keyword,
        value
   from ( select rid, keyword, value,
                count(*) over (partition by rid, keyword) rcount
           from w_data
         )
  /
         RID KEYWORD                        VALUE
  ---------- ------------------------------ ----------------------
     5716220 BANK_NUMBER                    17
     5716220 CHECK_AMOUNT                   $ 3797.68
     5716220 CHECK_DATE                     141211
     5716220 CHECK_NUMBER                   67714
     5716220 DOC_NAME                       Expense-Check
     5716220 EVENT_CODE_1                   141031INRA
     5716220 EVENT_CODE_2                   141107MSAM
     5716220 EVENT_CODE_3                   141107NVCC
     5716220 EVENT_CODE_4                   141107NVCC
     5716220 EVENT_CODE_5                   141113FLRC
     5716220 EVENT_CODE_6                   141113SCRCO
     5716220 FILENAME                       Q:teleform0133619
     5716220 INVOICE_NUMBER_1               789983
     5716220 INVOICE_NUMBER_2               791094
     5716220 INVOICE_NUMBER_3               791910
     5716220 INVOICE_NUMBER_4               792452
     5716220 INVOICE_NUMBER_5               792456
     5716220 INVOICE_NUMBER_6               DB791094
     5716220 VENDOR_NAME                    VTECH
     5716220 VENDOR_NUMBER                  1001685
     5716221 BANK_NUMBER                    17
     5716221 CHECK_AMOUNT                   $ 3797.68
     5716221 CHECK_DATE                     141211
     5716221 CHECK_NUMBER                   67714
     5716221 DOC_NAME                       Expense-Check
     5716221 EVENT_CODE_1                   141104MORER
     5716221 EVENT_CODE_2                   141113AZRAR
     5716221 EVENT_CODE_3                   141113SCRCO
     5716221 FILENAME                       Q:teleform01336191
     5716221 INVOICE_NUMBER_1               790437
     5716221 INVOICE_NUMBER_2               792508
     5716221 INVOICE_NUMBER_3               792959
     5716221 VENDOR_NAME                    VTECH
     5716221 VENDOR_NUMBER                  1001685
     5716222 BANK_NUMBER                    17
     5716222 CHECK_AMOUNT                   $ 7782.73
     5716222 CHECK_DATE                     141211
     5716222 CHECK_NUMBER                   67711
     5716222 DOC_NAME                       Expense-Check
     5716222 EVENT_CODE_1
     5716222 EVENT_CODE_2
     5716222 FILENAME                       Q:teleform0133616
     5716222 INVOICE_NUMBER_1               150533
     5716222 INVOICE_NUMBER_2               150562
     5716222 VENDOR_NAME                    TGRAPH
     5716222 VENDOR_NUMBER                  1001672
  46 rows selected.

[编辑]根据以下内容更新。并更好地匹配预期结果[/edit]

WITH子句只是在创建您的数据。。。您可以省略它，并将"from wdata"更改为您的表。

内部查询只获取数据，并统计每个"组"中不同关键字的出现次数。

外部查询使用CASE将计数器仅附加到EVENT_CODE和INVOICE_NUMBER。那里的新列为每个组使用row_number，并将其全部切片。

您可以将该查询返回到MERGE语句中，以获得最简单的更新方法：

  MERGE INTO <yourtable>  base
     USING ( <query above>
           ) new
        ON ( new.rid = base.rid
           and new.value = base.value
           and new.original_keyword = base.keyword )
     WHEN MATCHED THEN UPDATE
        SET base.keyword = new.keyword
  /

希望下面的帮助：

declare
INCR NUMBER:=1;
INCR1 NUMBER:=1;
CNT_EC NUMBER;
CNT_INV NUMBER;
begin
SELECT COUNT(KEYWORD) INTO CNT_EC FROM TABLE WHERE KEYWORD='EC';
SELECT COUNT(KEYWORD) INTO CNT_INV FROM TABLE WHERE KEYWORD='INVOICE_NUM';
IF CNT_EC>1THEN
FOR I IN(SELECT rid,keyword,value from table where rid>1 and keyword='EC') LOOP
UPDATE TABLE SET KEYWORD=I.KEYWORD||'_'||INCR  AND KEYWORD=I.KEYWORD AND VALUE=I.VALUE and rid=i.rid;
INCR:=INCR+1;
END LOOP;
END IF;
IF CNT_INV>1 THEN 
FOR J IN(SELECT rid,keyword,value from table where rid>1 and keyword='INVOICE_NUM') LOOP
UPDATE TABLE SET KEYWORD=J.KEYWORD||'_'||INCR1  AND KEYWORD=J.KEYWORD AND VALUE=J.VALUE and rid=j.rid;
INCR:=INCR1+1;
END LOOP;
END IF;
END;

相关内容

最新更新

热门标签：