ORACLE中兩表關聯UPDATE問題!

上一篇 / 下一篇 2008-04-22 09:15:52 / 个人分类：ORACLE 開發

今天在ITPUB上看見這樣一個問題:

有一张表如下 test：ITPUB个人空间[s3^i8P{k9S|
    value         numITPUB个人空间$i Jb#Kj!qB3m*QU
      15         1
NMt(M1W2irhB0      37         2ITPUB个人空间P6S2`%P'}CV F9h
      48         3
k1b2o D8C:?\j0                        2ITPUB个人空间:BZX|#Qbw:JC)\
                        3ITPUB个人空间A] P*bQ$F:f~o

p/Ag5gE]Eq0想根据同表当中num相同的值更新value为空的字段
4RS3MU#Fk@[0如想把两个空的value 值更新为 37,48ITPUB个人空间5Z5z`-BXo

現測試數據如下:

create table a7 as (select 15 value,1 num from dual
0K s+`3u&Rol5r0sg0                    unionITPUB个人空间Xe}O;V
                    select 37 value,2 num from dual
s&b1G:lF?R0                    union
@P(a9`a&@8d3c!R0                    select 48 value,3 num from dual
p4q9{3D }0                    unionITPUB个人空间]$qSKHa3_
                    select null value,2 num from dual
f7[E(S-j.Q0                    unionITPUB个人空间 BL8RH!t*W9R,B
                    select null value,3 num from dualITPUB个人空间3r5p_1tJO/S
                    )

方法一:

update a7 aITPUB个人空间(o Zb7F-_~mU+{` v*b
set value=(select value from a7 b where a.num=b.num and nvl(b.value,0)<>0)
4Zgu4N"_ D0where nvl(value,0)=0

方法二:

update (select num,value from a7 where nvl(value,0)=0) aITPUB个人空间%s1C"p8A%I
set value=(select value from a7 b where a.num=b.num and nvl(b.value,0)<>0)

這個看似很簡單的問題,我也想了一下,也寫過一些UPDATE語句,但是老報ORA-01427(单行子查询返回多于一行),后來自己分析了一下,總結了一些小經驗,當兩表關聯更新的時候,要更新的那個表(A表)和嵌套查詢的那個表(見紅色部門)的數據必須是一對一(兩表紀錄數一樣)或者多對一(子查詢的記錄數小于要更新的紀錄)的關系,千萬不要是一對多的關系,這樣才不會出錯!!!
1J we:aE/D0

第二道题是这样的:

表内容为：ITPUB个人空间d9h:\f7V'h
code       sal          salnum
j*^2D:}#E K,e0001       A             100ITPUB个人空间(TK8Q Gm:Q}t
001       B             200ITPUB个人空间f2G@#Xb7R
002       B             400
{q0U4Wk]L0002       C             200ITPUB个人空间~!pLwl"aF
003       D             200

C3OU:IN_$k0要得到如下格式的结果
*J M/WzR$f|n)G0code       sal          salnumITPUB个人空间D Vy V*tYrF4Q+m[
001       A             100ITPUB个人空间j+ZJ/J/R5w*w2@K
001       B             200
]*d5U9cU0001       C              ITPUB个人空间EN+v(aI'N
001       D              ITPUB个人空间zIr?1?.[
002       A
2M+`s(R(H1a3H0002       B             400ITPUB个人空间$`M2T ] h/FP6`
002       C             200ITPUB个人空间8Y%K(SO[']*`
002       D
5[T CM5z,~^0002       A              ITPUB个人空间D!Cr\a!\$`
002       B              ITPUB个人空间hv#Xg6?W^
002       C
:e/U.WN'j0002       D             200
.a.m1X,F)sf3Gr0对于这样的问题,我的思维刚开始就是想凭凑出这样的结果来,但是想了半天没有想出来,后来就要自己找规律啊,记录数(12)=(DISTINCT CODE)*(DISTINCT SAL),就是相当于两个表的笛卡尔集,这样一下思路就宽广多了啊!所以在以后的工作中,一定要善于发现规律,才能更好的解决问题,老想凑结果是不行的!

SQL> WITH A AS (SELECT '001' CODE,'A' SAL,100 SALNUM FROM DUALITPUB个人空间;C;f2R8CW%lF)kS
2             UNIONITPUB个人空间K7a,?_n ^(btNY J
3             SELECT '001' CODE,'B' SAL,200 SALNUM FROM DUAL
r{#\"~{!dD0f.Su0 4             UNION
Uk/L,H-?PeF\4NR0 5             SELECT '002' CODE,'B' SAL,400 SALNUM FROM DUALITPUB个人空间1SnOg\5E
6             UNIONITPUB个人空间\$oym,M#?(FLT&M
7             SELECT '002' CODE,'C' SAL,200 SALNUM FROM DUAL
cF!hc,S ?D$p0 8             UNION
6}iFHiu8M:k0 9             SELECT '003' CODE,'D' SAL,200 SALNUM FROM DUALITPUB个人空间] R8I&n1Iyfm
10             )
u3Upv_CP7L*E0 11 SELECT C.CODE,C.SAL,A.SALNUM FROM (select A.CODE,B.SAL from (SELECT DISTINCT CODE FROM A) A,(SELECT DISTINCT SAL FROM A) B
$PLZ+GgUawPI]0 12 ORDER BY CODE,SAL) C,AITPUB个人空间(lku#R p-OT+TI
13 WHERE A.CODE(+)=C.CODE AND A.SAL(+)=C.SAL
U0Q+Ve5oF.pdyZ:@0 14 ORDER BY C.CODE,C.SALITPUB个人空间:t5Q In|M_q,iB
15 /
Skl3sD2N#Bs0A0 ITPUB个人空间9|)x$a_1Ur
CODE SAL     SALNUM
2m&\"P2P1T0---- --- ----------ITPUB个人空间};}Tm(z8d
001 A          100ITPUB个人空间\JX%pb
001 B          200
"m1a^b:s1}8T0001 C
YE7J$Gu1iC]oD;]o0001 D  ITPUB个人空间@;HK,iz9_Q
002 A  ITPUB个人空间](_/}8l1H:`
002 B          400ITPUB个人空间qd+z_*|*f3n
002 C          200ITPUB个人空间7a_!LNZ%m)Dz
002 D
u qq7].CD0003 A
003 B
*WfY)M |uS+mn0003 C  ITPUB个人空间9iCG5i$d$QO
003 D          200ITPUB个人空间GeAWt:k6_O3p
ITPUB个人空间)o \5tU*dNa l
12 rows selected
s~zb$piG0K0

判斷紀錄是否是連續的?

SQL> with a as(select 2014 fphm,'00000001' kshm from dualITPUB个人空间$tI"^^g
2            unionITPUB个人空间\Pxg#BjH.m
3            select 2014 fphm,'00000002' kshm from dual
1L]#[}TX0 4            union
T A2z O+QAX0 5            select 2014 fphm,'00000003' kshm from dualITPUB个人空间l(fI4W$?*E}j}-eh
6            union
*C-X Zg|v*^9[CM0 7            select 2014 fphm,'00000004' kshm from dualITPUB个人空间"X}B8SIKQat
8            unionITPUB个人空间{iC `$JgR Ua#?
9            select 2014 fphm,'00000005' kshm from dual
^yYm@q,Q]0 10            unionITPUB个人空间E e+BW\S@qJ!k
11            select 2014 fphm,'00000007' kshm from dual
u2Seq"cpwbD0 12            union
-XQ,?0|U5Z1d0 13            select 2014 fphm,'00000008' kshm from dual
3D_&gmCO4t{0 14            unionITPUB个人空间1U5U`T^0Ki
15            select 2014 fphm,'00000009' kshm from dualITPUB个人空间 [*{n'qckl7gt0M(d
16            union
n7g8TzA0 17            select 2013 fphm,'00000120' kshm from dual
LXE)tP"?1K&C:p UI0 18            union
3Q nLO7Uidz1k-J0 19            select 2013 fphm,'00000121' kshm from dualITPUB个人空间[_(S}TJ\Y0m
20            unionITPUB个人空间$NbL5p mi+r9uA;r
21            select 2013 fphm,'00000122' kshm from dual
_`y2h1Q*?g+C`u0 22            union
)y.?+d GETe0 23            select 2013 fphm,'00000124' kshm from dual
*h?Y(`']9vc0 24            union
!VJB3g!GW*^V9~0 25            select 2013 fphm,'00000125' kshm from dualITPUB个人空间h:j~7EcR}#c
26            )ITPUB个人空间5\E0l|9|A?
27 SELECT b.fphm, MIN (b.kshm) Start_HM, MAX (b.kshm) End_HM
28 FROM (SELECT a.*,TO_NUMBER (a.kshm - ROWNUM)cc
29 FROM (SELECT *ITPUB个人空间!c/av]] v&z
30 FROM a
,M7~v*Ep i[0 31 ORDER BY fphm, kshm) a) b
32 GROUP BY b.fphm, b.ccITPUB个人空间3w.fCx+~
33 order by b.fphm, b.ccITPUB个人空间*F$Fe3pWhH
34 /
5s,[*ur:r N0 ITPUB个人空间3tN,fH4w&d
      FPHM START_HM END_HMITPUB个人空间8G%y{a X5Pb
---------- -------- --------ITPUB个人空间:Mn _j!M2U1jA9p
      2013 00000120 00000122ITPUB个人空间e;y6R%TA|w
      2013 00000124 00000125
4l/fb:a"e;{ o*L9N0      2014 00000001 00000005
SOf,pIe e0      2014 00000007 00000009