How to update all rows except one row out of many satisfying the given condition?

Question

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         | COLB     | COLC    |  COLD   |   COLE  |
-----------------------------------------------------------
|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 | MERGE   | Notes   |    Y    |
|Fiber        | ADD INFO | MULTI   | Notes   |    Y    |
|Fiber        | ADD INFO | KILO    | Notes   |    Y    |

Now i need to get the ouput like

__________________________________________________________
|COlA         | COLB     | COLC    |  COLD   |   COLE  |
-----------------------------------------------------------
|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 | MERGE   | Notes   |    Y    |
|Fiber        | ADD INFO | MULTI   | Notes   |    Y    |
|Fiber        | ADD INFO | KILO    | Notes   |    Y    |

I'm trying to update the COLE to 'N'(Except one row) from 'Y' .COLA,COLD,COLE should be same to update that particular set. If any row(COLA,COLC,COLD)combination has only one 'Y' then i should not update anything(Fiber in my sample data).I have to update the entire table. Can someone help me on this. Should i create a function and loop through it? In that also how to update only one row?

if you really don't care which row is not updated, then i would probably have a subselect which selects the MAX(all cols) and my outer select would have a NOT IN the sub select. I haven't put this in as answer as i don't have time to write the code but hopefully this will give you a steer — davegreen100, Jul 23 '15 at 10:13
Yeah i don't care which row is beign updated. I just want one of values in that set to get updated. ok thank you..I will try. But wen u have time pls try to post the answer. — Arun Palanisamy, Jul 23 '15 at 10:16
@Crazy2crack I see you are having `COLE = 'Y'` only for `colb = 'ADD INFO'`. If that' the case, then just update those rows to `'N'` where `colb <> 'ADD INFO'`. — Lalit Kumar B, Jul 23 '15 at 10:39
Hi Lalit, Sorry.Just for example i have given those scenerio. That will suit for only few rows ,But That is not the case for all the rows.I have around 5000 rows in that particular table. — Arun Palanisamy, Jul 23 '15 at 10:42

Lalit Kumar B · Accepted Answer · 2015-07-23T11:11:13.170

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

11 rows 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

11 rows selected.

Update statement

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.

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

11 rows selected.

SQL>

In this scenerio, the last row `Fiber` is also coming as `'N'` for me. Am i making some mistake anywhere? — Arun Palanisamy, Jul 23 '15 at 10:55
@Crazy2crack Are you sure there is only 1 row in your table for `Fiber`? Do one thing, add an `ORDER BY cola` and check again. — Lalit Kumar B, Jul 23 '15 at 10:59
I'm really very sry lalit. I have one more condition. `COLC` may vary. that is not constant. Updated my question. Sry for the inconvinience :( — Arun Palanisamy, Jul 23 '15 at 11:06
@Crazy2crack No problem, just add `colC` to `GROUP BY`. See my updated answer. — Lalit Kumar B, Jul 23 '15 at 11:12
If the table has an autoincrement id or any field with a unique constraint you may use that field in place of the orcale `RowID`. — danielson317, Nov 03 '21 at 13:33

score 2 · Answer 2 · answered Jul 23 '15 at 10:23

2

update table_name
set colE = 'N'
where rowid not in
(
select min(rowid)
from table_name
group by colA
)

answered Jul 23 '15 at 10:23

Simimmo

658
1
6
15

This will update all other rows also na. Like if i have only one entry with `COLE` has value 'Y'. I dont need to update anything corresponding to that row. But this s updating that also to 'N' . For Example 'Fiber' in my question – Arun Palanisamy Jul 23 '15 at 10:35

How to update all rows except one row out of many satisfying the given condition?

2 Answers2

Linked