如何在满足给定条件的许多行中更新除一行之外的所有行? [英] 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 | 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 |
现在我需要像
__________________________________________________________
|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 |
我正在尝试将 COLE
从 'Y' 更新为 'N'(除了一行).COLA,COLD,COLE
应该与更新相同特定集.如果任何行(COLA、COLC、COLD)组合只有一个Y",那么我不应该更新任何内容(示例数据中的 Fiber).我必须更新整个表.有人可以帮我解决这个问题.我应该创建一个函数并循环遍历它吗?在那也如何只更新一行?
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?
推荐答案
由于你在更新表时不关心行的顺序,你可以简单地使用 MIN 和 GROUPBY.
Since you don't care about the ordering of rows while updating the table, you could simply use MIN and 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)
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.
表格数据
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.
更新声明
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 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>
这篇关于如何在满足给定条件的许多行中更新除一行之外的所有行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!