ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE中兩表關聯UPDATE問題!

ORACLE中兩表關聯UPDATE問題!

原创 Linux操作系统 作者:jack198409 时间:2008-04-22 09:15:52 0 删除 编辑

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

 有一张表如下 test:
    value          num
      15              1
      37              2
      48              3
                        2
                        3

想根据同表当中num相同的值 更新value为空的字段
如想把两个空的value 值 更新为 37,48

 

現測試數據如下:

  create table a7 as (select 15 value,1 num from dual
                    union
                    select 37 value,2 num from dual
                    union
                    select 48 value,3 num from dual
                    union
                    select null value,2 num from dual
                    union
                    select null value,3 num from dual
                    )

 

方法一:

 update  a7 a
set value=(select value from a7 b where a.num=b.num and nvl(b.value,0)<>0)
where nvl(value,0)=0

方法二:

 update (select num,value from a7 where nvl(value,0)=0)  a
set value=(select value from a7 b where a.num=b.num and nvl(b.value,0)<>0)

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

 

第二道题是这样的:

表内容为:
code        sal            salnum
001          A               100
001          B               200
002          B                400
002          C                200
003          D                200

要得到如下格式的结果
code        sal            salnum
001          A              100
001          B              200
001          C              
001          D              
002          A              
002          B              400
002          C              200
002          D              
002          A              
002          B              
002          C              
002          D              200

对于这样的问题,我的思维刚开始就是想凭凑出这样的结果来,但是想了半天没有想出来,后来就要自己找规律啊,记录数(12)=(DISTINCT CODE)*(DISTINCT SAL),就是相当于两个表的笛卡尔集,这样一下思路就宽广多了啊!所以在以后的工作中,一定要善于发现规律,才能更好的解决问题,老想凑结果是不行的!

SQL> WITH A AS (SELECT '001' CODE,'A' SAL,100 SALNUM FROM DUAL
  2             UNION
  3             SELECT '001' CODE,'B' SAL,200 SALNUM FROM DUAL
  4             UNION
  5             SELECT '002' CODE,'B' SAL,400 SALNUM FROM DUAL
  6             UNION
  7             SELECT '002' CODE,'C' SAL,200 SALNUM FROM DUAL
  8             UNION
  9             SELECT '003' CODE,'D' SAL,200 SALNUM FROM DUAL
 10             )
 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
 12  ORDER BY CODE,SAL) C,A
 13  WHERE A.CODE(+)=C.CODE AND A.SAL(+)=C.SAL
 14  ORDER BY C.CODE,C.SAL
 15  /
 
CODE SAL     SALNUM
---- --- ----------
001  A          100
001  B          200
001  C  
001  D  
002  A  
002  B          400
002  C          200
002  D  
003  A  
003  B  
003  C  
003  D          200
 
12 rows selected
 

 

判斷紀錄是否是連續的?

SQL> with a as(select 2014 fphm,'00000001' kshm from dual
  2            union
  3            select 2014 fphm,'00000002' kshm from dual
  4            union
  5            select 2014 fphm,'00000003' kshm from dual
  6            union
  7            select 2014 fphm,'00000004' kshm from dual
  8            union
  9            select 2014 fphm,'00000005' kshm from dual
 10            union
 11            select 2014 fphm,'00000007' kshm from dual
 12            union
 13            select 2014 fphm,'00000008' kshm from dual
 14            union
 15            select 2014 fphm,'00000009' kshm from dual
 16            union
 17            select 2013 fphm,'00000120' kshm from dual
 18            union
 19            select 2013 fphm,'00000121' kshm from dual
 20            union
 21            select 2013 fphm,'00000122' kshm from dual
 22            union
 23            select 2013 fphm,'00000124' kshm from dual
 24            union
 25            select 2013 fphm,'00000125' kshm from dual
 26            )
 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 *
 30  FROM a
 31  ORDER BY fphm, kshm) a) b
 32  GROUP BY b.fphm, b.cc
 33  order by b.fphm, b.cc
 34  /
 
      FPHM START_HM END_HM
---------- -------- --------
      2013 00000120 00000122
      2013 00000124 00000125
      2014 00000001 00000005
      2014 00000007 00000009

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10768286/viewspace-246426/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-13

  • 博文量
    135
  • 访问量
    285507