如何更新除满足给定条件的一行中的一行以外的所有行？

如何更新除满足给定条件的一行中的一行以外的所有行？

由于您在更新表时无需关心行的顺序，因此可以简单地使用 MIN 和 GROUP BY 。

更新 您需要按

colA

和分组

colC

。

例如，

设置

SQL> CREATE TABLE t  2    (  3      COlA VARCHAr2(12),  4      COLB VARCHAr2(9),  5      COLC VARCHAr2(5),  6      COLD VARCHAr2(5),  7      COLE VARCHAr2(1)  8    );Table created.SQL> INSERT ALL  2      INTO t (COlA, COLB, COLC, COLD, COLE)  3VALUES ('Equipment SI', 'ADD INFO', 'MERGE', 'Notes', 'Y')  4      INTO t (COlA, COLB, COLC, COLD, COLE)  5VALUES ('Equipment SI', 'Active', 'MERGE', 'Notes', 'Y')  6      INTO t (COlA, COLB, COLC, COLD, COLE)  7VALUES ('Equipment SI', 'ORIGINAL', 'MERGE', 'Notes', 'Y')  8      INTO t (COlA, COLB, COLC, COLD, COLE)  9VALUES ('Fastening', 'ADD INFO', 'MERGE', 'Notes', 'Y') 10      INTO t (COlA, COLB, COLC, COLD, COLE) 11VALUES ('Fastening', 'Active', 'MERGE', 'Notes', 'Y') 12      INTO t (COlA, COLB, COLC, COLD, COLE) 13VALUES ('Electonics', 'ADD INFO', 'MERGE', 'Notes', 'Y') 14      INTO t (COlA, COLB, COLC, COLD, COLE) 15VALUES ('Electonics', 'Active O', 'MERGE', 'Notes', 'Y') 16      INTO t (COlA, COLB, COLC, COLD, COLE) 17VALUES ('Electonics', 'ORIGINAL', 'MERGE', 'Notes', 'Y') 18      INTO t (COlA, COLB, COLC, COLD, COLE) 19VALUES ('Electonics', 'Nominated', 'MERGE', 'Notes', 'Y') 20      INTO t (COlA, COLB, COLC, COLD, COLE) 21VALUES ('Fiber', 'ADD INFO', 'MULTI', 'Notes', 'Y') 22      INTO t (COlA, COLB, COLC, COLD, COLE) 23VALUES ('Fiber', 'ADD INFO', 'KILO', 'Notes', 'Y') 24  SELECt * FROM dual;11 rows created.SQL> COMMIT;Commit complete.

表格数据

SQL> SELECt * FROM t;COLA         COLB      COLC  COLD  C------------ --------- ----- ----- -Equipment SI ADD INFO  MERGE Notes YEquipment SI Active    MERGE Notes YEquipment SI ORIGINAL  MERGE Notes YFastening    ADD INFO  MERGE Notes YFastening    Active    MERGE Notes YElectonics   ADD INFO  MERGE Notes YElectonics   Active O  MERGE Notes YElectonics   ORIGINAL  MERGE Notes YElectonics   Nominated MERGE Notes YFiber        ADD INFO  MULTI Notes YFiber        ADD INFO  KILO  Notes Y11 rows selected.

更新声明

SQL> UPDATE t  2  SET colE         = 'N'  3  WHERe ROWID NOT IN  4    ( SELECT MIN(rowid) FROM t GROUP BY colA, colC  5    );6 rows updated.

让我们 检查一下

SQL> SELECT * FROM t;COLA         COLB      COLC  COLD  C------------ --------- ----- ----- -Equipment SI ADD INFO  MERGE Notes YEquipment SI Active    MERGE Notes NEquipment SI ORIGINAL  MERGE Notes NFastening    ADD INFO  MERGE Notes YFastening    Active    MERGE Notes NElectonics   ADD INFO  MERGE Notes YElectonics   Active O  MERGE Notes NElectonics   ORIGINAL  MERGE Notes NElectonics   Nominated MERGE Notes NFiber        ADD INFO  MULTI Notes YFiber        ADD INFO  KILO  Notes Y11 rows selected.SQL>