Clever Geek Handbook

How do I update all but one of the many rows that satisfy a given condition?

I have a scenerio where I have to update all rows, but one of many rows. Let's 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 output 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 am trying to update COLE

to 'N' (other than one line) from 'Y'. COLA,COLD,COLE

should be the same to update this particular set. If any combination of rows (COLA, COLC, COLD) has only one "Y" then I shouldn't update anything (Fiber in my sample data). I need to update the whole table. Can someone help me on this. Should I create a function and skip it? In this also how to update only one row?

+3


source to share


2 answers


Since you don't need to order the rows when updating the table, you can just use MIN and GROUP BY .

Refresh . You need to group by colA

and colC

.

For example,

Customization

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 operator

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 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>
+3


source 


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


+1


source






More articles:


All Articles