ORACLE中兩表關聯UPDATE問題!-优快云博客

本文探讨了Oracle SQL中解决特定数据更新问题的方法，包括如何避免ORA-01427错误，并通过实例展示了如何生成复杂的交叉组合数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天在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