由于您在更新表时无需关心行的顺序，因此可以简单地使用 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>
