oracle中的表更新 [英] Table update in oracle
本文介绍了oracle中的表更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
ID VAL CNT
1-45 -5
2- 40 -8
3- 30 -9
4- 78 -6
5- 88 -4
6- 0 -0
7- 0 -0
8- 0 -0
9-45 -8
10 - 83 -9
11- 0 -0
我想更新CNT为0的所有VAL列其先前的值CNT> 0.所以,o / p应该是:
ID VAL CNT
1-45 -5
2- 40 -8
3- 30 -9
4- 78 -6
5- 88 -4
6- 88 -0
7-88 - 0
8-88 -0
9-45 -8
10-83 -9
11-83 -0
我知道这是一个愚蠢的问题,但我被卡住了。
任何帮助?
谢谢
解决方案
这是一个选择,它将为您提供所需的值,现在您只需要合并它们。
WITH 已连接为(
SELECT ID
,LAG(ID, 1 ) OVER ( ORDER BY ID)PREVID
,VAL
,CNT
FROM Table1
)
,CTE(ID,VAL,CNT) as (
SELECT ID
,VAL
,CNT
FROM 已连接
WHERE PREVID IS NULL
UNION 所有
SELECT c.ID
,NVL( NULLIF (c.VAL,< span class =code-digit> 0 ),CTE.VAL)VAL
,c.CNT
FROM connected c
JOIN CTE
ON CTE.ID = c.PREVID
)
SELECT ID,VAL,CNT
FROM CTE
ID VAL CNT
1- 45 -5
2- 40 -8
3- 30 -9
4- 78 -6
5- 88 -4
6- 0 -0
7- 0 -0
8- 0 -0
9- 45 -8
10- 83 -9
11- 0 -0
I want to update the all the VAL column where CNT is 0 with its previous value where where CNT > 0.So, the o/p should be :
ID VAL CNT
1- 45 -5
2- 40 -8
3- 30 -9
4- 78 -6
5- 88 -4
6- 88 -0
7- 88 -0
8- 88 -0
9- 45 -8
10- 83 -9
11- 83 -0
I know it is a silly question,but i am stuck.
Any help?
Thanks
解决方案
Here's a select that will give you the values needed, now you just need to merge them.
WITH connected as ( SELECT ID ,LAG(ID,1) OVER(ORDER BY ID) PREVID ,VAL ,CNT FROM Table1 ) ,CTE(ID,VAL,CNT) as ( SELECT ID ,VAL ,CNT FROM connected WHERE PREVID IS NULL UNION ALL SELECT c.ID ,NVL(NULLIF(c.VAL,0),CTE.VAL) VAL ,c.CNT FROM connected c JOIN CTE ON CTE.ID = c.PREVID ) SELECT ID,VAL,CNT FROM CTE
这篇关于oracle中的表更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文