How To Update All Rows Except One Row Out Of Many Satisfying The Given Condition?

I have a scenerio where i have to update all the rows but one out of many rows.Say I have a table like __________________________________________________________ |COlA | C

Solution 1:

Since you don't care about the ordering of rows while updating the table, you could simply use MIN and GROUP BY.

Update You need to group by colA and colC.

For example,

Setup

SQL>CREATETABLE 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>INSERTALL2INTO t (COlA, COLB, COLC, COLD, COLE)
  3VALUES ('Equipment SI', 'ADD INFO', 'MERGE', 'Notes', 'Y')
  4INTO t (COlA, COLB, COLC, COLD, COLE)
  5VALUES ('Equipment SI', 'Active', 'MERGE', 'Notes', 'Y')
  6INTO t (COlA, COLB, COLC, COLD, COLE)
  7VALUES ('Equipment SI', 'ORIGINAL', 'MERGE', 'Notes', 'Y')
  8INTO t (COlA, COLB, COLC, COLD, COLE)
  9VALUES ('Fastening', 'ADD INFO', 'MERGE', 'Notes', 'Y')
 10INTO t (COlA, COLB, COLC, COLD, COLE)
 11VALUES ('Fastening', 'Active', 'MERGE', 'Notes', 'Y')
 12INTO t (COlA, COLB, COLC, COLD, COLE)
 13VALUES ('Electonics', 'ADD INFO', 'MERGE', 'Notes', 'Y')
 14INTO t (COlA, COLB, COLC, COLD, COLE)
 15VALUES ('Electonics', 'Active O', 'MERGE', 'Notes', 'Y')
 16INTO t (COlA, COLB, COLC, COLD, COLE)
 17VALUES ('Electonics', 'ORIGINAL', 'MERGE', 'Notes', 'Y')
 18INTO t (COlA, COLB, COLC, COLD, COLE)
 19VALUES ('Electonics', 'Nominated', 'MERGE', 'Notes', 'Y')
 20INTO t (COlA, COLB, COLC, COLD, COLE)
 21VALUES ('Fiber', 'ADD INFO', 'MULTI', 'Notes', 'Y')
 22INTO t (COlA, COLB, COLC, COLD, COLE)
 23VALUES ('Fiber', 'ADD INFO', 'KILO', 'Notes', 'Y')
 24SELECT*FROM dual;

11rows created.

SQL>COMMIT;

Commit complete.

Table data

SQL>SELECT*FROM t;

COLA         COLB      COLC  COLD  C
------------ --------- ----- ----- -
Equipment SI ADD INFO  MERGE Notes Y
Equipment SI Active    MERGE Notes Y
Equipment SI ORIGINAL  MERGE Notes Y
Fastening    ADD INFO  MERGE Notes Y
Fastening    Active    MERGE Notes Y
Electonics   ADD INFO  MERGE Notes Y
Electonics   Active O  MERGE Notes Y
Electonics   ORIGINAL  MERGE Notes Y
Electonics   Nominated MERGE Notes Y
Fiber        ADD INFO  MULTI Notes Y
Fiber        ADD INFO  KILO  Notes Y

11rows selected.

Update statement

SQL>UPDATE t
  2SET colE         ='N'3WHERE ROWID NOTIN4    ( SELECTMIN(rowid) FROM t GROUPBY colA, colC
  5    );

6rows updated.

Let's check

SQL>SELECT*FROM t;

COLA         COLB      COLC  COLD  C
------------ --------- ----- ----- -
Equipment SI ADD INFO  MERGE Notes Y
Equipment SI Active    MERGE Notes N
Equipment SI ORIGINAL  MERGE Notes N
Fastening    ADD INFO  MERGE Notes Y
Fastening    Active    MERGE Notes N
Electonics   ADD INFO  MERGE Notes Y
Electonics   Active O  MERGE Notes N
Electonics   ORIGINAL  MERGE Notes N
Electonics   Nominated MERGE Notes N
Fiber        ADD INFO  MULTI Notes Y
Fiber        ADD INFO  KILO  Notes Y

11rows selected.

SQL>

Solution 2:

update table_name
set colE ='N'where rowid notin
(
selectmin(rowid)
from table_name
groupby colA
)