Oracle 通过从同一表或联接表中获取输出来更新表

嗨，

我需要通过使用分组函数从同一表中获取所选数据来更新表的一列。您能建议一些示例查询吗？
例如: 我的数据如下:

FOM_SAP_VKORG SDB_SAP_MATNR SDB_DATAB SDB_DATBI FOM_DATAB FOM_DATBI RESULT_TYPE D_RECON_DATE ROWNUMBER
M100 000000000002043171 20161224 99991231 20161224 20170220 VALID_TO_MISMATCH 05/04/2017 1
M100 000000000002043171 20170221 99991231 20170221 99991231 MATCHED 05/04/2017 2
复制

现在，我想用第二条记录中的SDB_DATAB值覆盖第一条记录的SDB_DATBI。因此，我必须覆盖result_type = 'valid_to_mismatch' 的所有行的值。
你能建议最好的查询吗？我写了下面的查询，但它花了太长时间，它没有完成:

UPDATE sap_recon_sdb_ret_price_dtl2 x
   SET SDB_DATBI = (SELECT to_char(max(to_date(sdb_datab,'YYYYMMDD')) -1,'YYYYMMDD')
         FROM sap_recon_sdb_ret_price_dtl2 y
        WHERE --y.sdb_sap_matnr   = '000000000002043171'AND 
           y.sdb_sap_kotabnr   = x.sdb_sap_kotabnr
           AND y.sdb_sap_kschl   = x.sdb_sap_kschl
           AND y.sdb_sap_vtweg   = x.sdb_sap_vtweg
           AND y.sdb_sap_vkorg   = x.sdb_sap_vkorg
           AND NVL(y.sdb_sap_pltyp,'$') = NVL(x.sdb_sap_pltyp,'$')
           AND y.sdb_sap_matnr   = x.sdb_sap_matnr
           AND y.sdb_sap_vrkme   = x.sdb_sap_vrkme
           and y.ROWNUMBER    = x.rownumber + 1)
 WHERE x.result_type    = 'VALID_TO_MISMATCH'
   and exists (SELECT 'X'
      FROM sap_recon_sdb_ret_price_dtl2 y
      WHERE --y.sdb_sap_matnr   = '000000000002043171' AND
           y.sdb_sap_kotabnr   = x.sdb_sap_kotabnr
          AND y.sdb_sap_kschl   = x.sdb_sap_kschl
          AND y.sdb_sap_vtweg   = x.sdb_sap_vtweg
          AND y.sdb_sap_vkorg   = x.sdb_sap_vkorg
          AND NVL(y.sdb_sap_pltyp,'$') = NVL(x.sdb_sap_pltyp,'$')
          AND y.sdb_sap_matnr   = x.sdb_sap_matnr
          AND y.sdb_sap_vrkme   = x.sdb_sap_vrkme
          and y.rownumber    = x.rownumber + 1)
复制

如果我的详细信息足够清楚，请告诉我。谢谢。
问候，
阿米特

到底是什么

“花了太长时间，还没有完成”

意思是？你的例子对我来说运行得很好，很快:

SQL> set timing on
SQL> CREATE TABLE SAP_RECON_SDB_RET_PRICE_DTL2
  2  (
  3    SDB_SAP_KOTABNR    VARCHAR2(1 BYTE),
  4    SDB_SAP_KSCHL      VARCHAR2(4 BYTE),
  5    SDB_SAP_VKORG      VARCHAR2(4 BYTE),
  6    SDB_SAP_VTWEG      VARCHAR2(2 BYTE),
  7    SDB_SAP_WERKS      VARCHAR2(4 BYTE),
  8    SDB_SAP_PLTYP      VARCHAR2(2 BYTE),
  9    SDB_SAP_MATNR      VARCHAR2(18 BYTE),
 10    SDB_SAP_VRKME      VARCHAR2(3 BYTE),
 11    SDB_KBETR          NUMBER,
 12    SDB_KONWA          VARCHAR2(5 BYTE),
 13    SDB_KPEIN          NUMBER(5),
 14    SDB_SAP_KMEIN      VARCHAR2(3 BYTE),
 15    SDB_DATAB          VARCHAR2(8 BYTE),
 16    SDB_DATBI          VARCHAR2(8 BYTE),
 17    FOM_SAP_KOTABNR    VARCHAR2(1 BYTE),
 18    FOM_SAP_KSCHL      VARCHAR2(4 BYTE),
 19    FOM_SAP_VKORG      VARCHAR2(4 BYTE),
 20    FOM_SAP_VTWEG      VARCHAR2(2 BYTE),
 21    FOM_SAP_WERKS      VARCHAR2(4 BYTE),
 22    FOM_SAP_PLTYP      VARCHAR2(2 BYTE),
 23    FOM_COUNTRY_IDEN   VARCHAR2(30 BYTE),
 24    FOM_SAP_MATNR      VARCHAR2(18 BYTE),
 25    FOM_SAP_VRKME      VARCHAR2(3 BYTE),
 26    FOM_KBETR          VARCHAR2(22 BYTE),
 27    FOM_KONWA          VARCHAR2(5 BYTE),
 28    FOM_KPEIN          NUMBER(22),
 29    FOM_SAP_KMEIN      VARCHAR2(3 BYTE),
 30    FOM_DATAB          VARCHAR2(8 BYTE),
 31    FOM_DATBI          VARCHAR2(8 BYTE),
 32    RESULT_TYPE        VARCHAR2(2000 BYTE),
 33    D_RECON_DATE       DATE,
 34    V_REMARKS          VARCHAR2(2000 BYTE),
 35    UPLOADED_VALID_TO  VARCHAR2(11 BYTE),
 36    ROWNUMBER          NUMBER
 37  );

Table created.

Elapsed: 00:00:00.33
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab,
sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab
, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values
  4     ('1', 'VKP0', 'B100', '01', '000000000002043171', 'KG', 1.69, 'BND', 1, 'KG', '20161221', '99991231', '1', 'VKP0', 'B100', '01', '000000000002043171', '
KG', '1.69', 'BND', 1, 'KG', '20161221', '20170328', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.31
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab,
sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab
, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values
  4     ('1', 'VKP0', 'B100', '01', '000000000002043171', 'KG', 1.99, 'BND', 1, 'KG', '20170329', '99991231', '1', 'VKP0', 'B100', '01', '000000000002043171', '
KG', '1.99', 'BND', 1, 'KG', '20170329', '20170403', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);

1 row created.

Elapsed: 00:00:00.31
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kmein, sdb_datab,
sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_kpein, fom_sap_kmein, fom_datab
, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values    ('1', 'VKP0', 'M100', '01', '000000000002043171', 'KG', 4.99, 'MYR', 1, 'KG', '20161224', '99991231', '1', 'VKP0', 'M100', '01', '00000000000204
3171', 'KG', '4.99', 'MYR', 1, 'KG', '20161224', '20170220', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.31
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kme
in, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_k
pein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values    ('2', 'VKP0', 'M100', '01', 'P1', '000000000002043171', 'KG', 3.59, 'MYR', 1, 'KG', '20161201', '99991231', '2', 'VKP0', 'M100', '01', 'P1', '00
0000000002043171', 'KG', '3.59', 'MYR', 1, 'KG', '20161201', '20170331', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.32
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kme
in, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_k
pein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values    ('2', 'VKP0', 'M100', '01', 'P2', '000000000002043171', 'KG', 3.59, 'MYR', 1, 'KG', '20161201', '99991231', '2', 'VKP0', 'M100', '01', 'P2', '00
0000000002043171', 'KG', '3.59', 'MYR', 1, 'KG', '20161201', '20170331', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.31
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kme
in, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_k
pein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values    ('2', 'VKP0', 'M100', '01', 'P3', '000000000002043171', 'KG', 4.99, 'MYR', 1, 'KG', '20161224', '99991231', '2', 'VKP0', 'M100', '01', 'P3', '00
0000000002043171', 'KG', '4.99', 'MYR', 1, 'KG', '20161224', '20170220', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.31
SQL>
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kme
in, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_k
pein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values    ('2', 'VKP0', 'M100', '01', 'P5', '000000000002043171', 'KG', 6.99, 'MYR', 1, 'KG', '20161011', '99991231', '2', 'VKP0', 'M100', '01', 'P5', '00
0000000002043171', 'KG', '6.99', 'MYR', 1, 'KG', '20161011', '20170313', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.42
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kme
in, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_k
pein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values    ('2', 'VKP0', 'M100', '01', 'P6', '000000000002043171', 'KG', 6.99, 'MYR', 1, 'KG', '20161011', '99991231', '2', 'VKP0', 'M100', '01', 'P6', '00
0000000002043171', 'KG', '6.99', 'MYR', 1, 'KG', '20161011', '20170313', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.31
SQL> Insert into SAP_RECON_SDB_RET_PRICE_DTL2
  2     (sdb_sap_kotabnr, sdb_sap_kschl, sdb_sap_vkorg, sdb_sap_vtweg, sdb_sap_pltyp, sdb_sap_matnr, sdb_sap_vrkme, sdb_kbetr, sdb_konwa, sdb_kpein, sdb_sap_kme
in, sdb_datab, sdb_datbi, fom_sap_kotabnr, fom_sap_kschl, fom_sap_vkorg, fom_sap_vtweg, fom_sap_pltyp, fom_sap_matnr, fom_sap_vrkme, fom_kbetr, fom_konwa, fom_k
pein, fom_sap_kmein, fom_datab, fom_datbi, result_type, d_recon_date, rownumber)
  3   Values
  4     ('2', 'VKP0', 'M101', '01', 'P4', '000000000002043171', 'KG', 7.49, 'MYR', 1, 'KG', '20161011', '99991231', '2', 'VKP0', 'M101', '01', 'P4', '0000000000
02043171', 'KG', '7.49', 'MYR', 1, 'KG', '20161011', '20170313', 'VALID_TO_MISMATCH', TO_DATE('04/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);

1 row created.

Elapsed: 00:00:00.31
SQL>
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.32
SQL>
SQL> UPDATE sap_recon_sdb_ret_price_dtl2 x
  2     SET SDB_DATBI = (
  3        SELECT to_char(max(to_date(sdb_datab,'YYYYMMDD')) -1,'YYYYMMDD')
  4                FROM sap_recon_sdb_ret_price_dtl2 y
  5               WHERE --y.sdb_sap_matnr      =  '000000000002043171' AND
  6                 y.sdb_sap_kotabnr      =  x.sdb_sap_kotabnr
  7                 AND y.sdb_sap_kschl      =  x.sdb_sap_kschl
  8                 AND y.sdb_sap_vtweg      =  x.sdb_sap_vtweg
  9                 AND y.sdb_sap_vkorg      =  x.sdb_sap_vkorg
 10                 AND NVL(y.sdb_sap_pltyp,'$')  =  NVL(x.sdb_sap_pltyp,'$')
 11                 AND y.sdb_sap_matnr      =  x.sdb_sap_matnr
 12                 AND y.sdb_sap_vrkme      =  x.sdb_sap_vrkme
 13                 and y.ROWNUMBER        =  x.rownumber + 1
 14             group by y.sdb_sap_matnr,y.sdb_sap_kotabnr,y.sdb_sap_kschl,y.sdb_sap_vtweg,y.sdb_sap_vkorg,NVL(y.sdb_sap_pltyp,'$'),y.sdb_sap_vrkme
 15   )
 16   WHERE x.result_type        =  'VALID_TO_MISMATCH'
 17     and exists (SELECT 'X'
 18            FROM sap_recon_sdb_ret_price_dtl2 y
 19            WHERE y.sdb_sap_kotabnr      =  x.sdb_sap_kotabnr
 20               AND y.sdb_sap_kschl      =  x.sdb_sap_kschl
 21               AND y.sdb_sap_vtweg      =  x.sdb_sap_vtweg
 22               AND y.sdb_sap_vkorg      =  x.sdb_sap_vkorg
 23               AND NVL(y.sdb_sap_pltyp,'$')  =  NVL(x.sdb_sap_pltyp,'$')
 24               AND y.sdb_sap_matnr      =  x.sdb_sap_matnr
 25               AND y.sdb_sap_vrkme      =  x.sdb_sap_vrkme
 26               and y.rownumber        =  x.rownumber + 1) ;

1 row updated.

Elapsed: 00:00:00.33
复制

我猜这是因为你的真实表有更多的数据。如果是这样，您可以通过以下方式获得一些里程:

-在标量子查询中选择下一个日期 (顺便说一句: 请使用 “日期” 而不是varchar2来存储日期!)
-在子查询不为null的情况下更新此查询。

例如:

update (SELECT SDB_DATBI, 
       (select to_char(max(to_date(sdb_datab,'YYYYMMDD'))-1,'YYYYMMDD')
        from sap_recon_sdb_ret_price_dtl2 x
        where y.sdb_sap_kotabnr      =  x.sdb_sap_kotabnr 
        AND y.sdb_sap_kschl      =  x.sdb_sap_kschl 
        AND y.sdb_sap_vtweg      =  x.sdb_sap_vtweg 
        AND y.sdb_sap_vkorg      =  x.sdb_sap_vkorg 
        AND NVL(y.sdb_sap_pltyp,'$')  =  NVL(x.sdb_sap_pltyp,'$') 
        AND y.sdb_sap_matnr      =  x.sdb_sap_matnr 
        AND y.sdb_sap_vrkme      =  x.sdb_sap_vrkme 
        and y.rownumber        =  x.rownumber + 1) mx
FROM   sap_recon_sdb_ret_price_dtl2 y )
set    SDB_DATBI = mx
where  mx is not null;
复制

注意: 这假设每个组只能有2个行号。如果不是这种情况，您将需要一种不同的方法。

如果这没有帮助，请发布更新的执行计划。您可以通过运行:

set serveroutput off
alter session set statistics_level = all;

... your update statement ...

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
复制

然后向我们发送 “从表中选择 *...” 查询的输出。