念头:6 n. } p* d0 G3 _! E3 a/ v1 b
想在Oracle赌暌姑一条SQL语句直接进行Insert/Update的操作。) |$ s% y }9 Y3 h
声名:
: w/ r# h; o( B 在进行SQL语句编写时,我们经常会碰着年夜量的同时进行Insert/Update的语句 ,也就是说当存在记势瘫,就更新(Update),不存在数据时,就插入(Insert)。
! @! f O$ w; D: a 拭魅战:' b3 e1 L+ I& `5 m Y* F" q, x
接下来我们有一个使命,有一个表T,有两个字段a,b,我们想在表T中做Insert/Update,如不美观存在,则更新T中b的值,如不美观不存在,则插入一标识表记标帜录。在Microsoft的SQL语法中,很简单的一句判定就可以了,SQL Server中的语法如下:4 ]7 r6 b9 o- i4 Y& H2 m" p% c
if exists(select 1 from T where T.a=''1001'' ) update T set T.b=2 Where T.a=''1001'' else insert into T(a,b) values(''1001'',2);$ q( ?' |! w$ A9 H) L( s) p' _+ J8 e
以上语句剖明当T表中如不美观存在a=''1001'' 的记实的话,就把b的值设为2,否则就Insert一条a=''100'',b=2的记实到T中。, p! G% T* R: `3 \$ _, W
可是接下来在Oracle中就碰着麻烦了,记得在Oracle 9i之后就有一条Merge into 的语句可以同时进行Insert 和Update的吗,Merge的语法如下:
) F7 n. m" v' ^0 m- H, w MERGE INTO table_name alias1) }6 }, e! R( n5 j9 J1 ?
USING (table|view|sub_query) alias20 b+ R/ I) | H {$ P% [: p
ON (join condition)
+ X) [3 H5 ], t6 j3 W9 S. t- ] WHEN MATCHED THEN/ @. Z( y8 q: G+ G0 w. ^. J
UPDATE table_name, b, x- |, v# @2 H. M$ t$ _
SET col1 = col_val1,1 F$ q3 z; @& r( M. ^
col2 = col2_val" S6 G9 ^. p9 B [7 |! K
WHEN NOT MATCHED THEN( j+ n. s' c0 O# \+ F y! v) {4 c
INSERT (column_list) VALUES (column_values);
9 E0 d9 R" c8 w 膳缦沔的语法巨匠应该都轻易懂吧,那我们按照以上的逻辑再写一次。
9 @3 n1 v1 u5 B/ L9 _6 e MERGE INTO T T1& u" o _( A0 v! p# \/ Y
USING (SELECT a,b FROM T WHERE t.a=''1001'') T2+ h1 d/ M3 T" M" d# i v
ON ( T1.a=T2.a), V4 z( S7 N$ I' U
WHEN MATCHED THEN
! f. i) w2 q4 w UPDATE SET T1.b = 2
& @3 R0 }5 g* V/ w WHEN NOT MATCHED THEN7 N) w# p d( x% f. _2 t2 F
INSERT (a,b) VALUES(''1001'',2);
1 I* {+ ?( z5 ` 以上的语句貌似很对是吧,现实上,该语句只能进行更新,而无法进行Insert,错误在哪里呢?2 d5 y2 {# f' t8 l- m( P# h& y& o5 M
其其实Oracle中Merge语句原先是用来进行整表的更新用的,也就是ETL工具斗劲常用的语法,重点是在Using上。
; z9 M) f" S0 q: W2 Y( n9 q6 @ 用中文来诠释Merge语法,就是:5 u) P! u6 D$ w& ~
在alias2中Select出来的数据,每一条都跟alias1进行 ON (join condition)的斗劲,如不美观匹配,就进行更新的操作(Update),如不美观不匹配,就进行插入操作(Insert)。
3 C( }1 B! Y4 M* V$ q: z) l 是以,严酷意义上讲,”在一个同时存在Insert和Update语法的Merge语句中,总共Insert/Update的记实数,就是Using语句中alias2的记实数。”, n% Z7 N; @& \
以上这句话也就很好的诠释了在膳缦沔写的语句为何只能进行Update,而不能进行Insert了,因为都Select不到数据,若何能进行Insert呢:)
. a7 z$ }$ e' k/ o 接下来要改成正确的语句就轻易多了,如下:( D A) T D- S" v! t$ }
MERGE INTO T T1: \( k- U" y% N `. \
USING (SELECT ''1001'' AS a,2 AS b FROM dual) T2, x5 c6 B: a( |" I9 {- s7 x8 H
ON ( T1.a=T2.a)
- }* p8 h4 a& j WHEN MATCHED THEN3 `2 B. d+ y/ k$ `, _
UPDATE SET T1.b = T2.b) o+ R: o1 r! u4 V) s* L
WHEN NOT MATCHED THEN
L& b- y1 Y3 w+ p4 N$ f, s H- W/ i INSERT (a,b) VALUES(T2.a,T2.b);! N, W( H! X6 j. U" T: b9 R
发芽结不美观,OK!
1 u* m# Y! P$ C' o5 Y) p 注重:
! x- t6 x( W5 H5 g* O* k 如不美观不懂Merge语句的事理,Merge语句是一条斗劲危险的语句,出格是在您只想更新一标识表记标帜录的时辰,因为不经意间,你可能就把整表的数据都Update了一遍.; o( s' d( ~8 k- o% N
我曾经犯过的一个错误如下所示,巨匠看出来是侍趵硎题了吗? \4 J6 _! n: L3 x4 |
MERGE INTO T T13 x) ^6 W% R1 ?6 N4 [5 t9 M6 r6 A t
USING (SELECT Count(*) cnt FROM T WHERE T.a=''1001'') T2# o; `: [; P9 z4 e$ h$ G" e
ON (T2.cnt>0)
. \8 f7 [5 U1 @4 e" E WHEN MATCHED THEN
% h8 @/ n& r2 A* u! r( T UPDATE SET T1.b = T2.b6 B- ~6 K7 i& g) y% e: e9 W1 \
WHEN NOT MATCHED THEN: Q. Q+ M5 v s4 n! ^5 H! |) Q
INSERT (a,b) VALUES(T2.a,T2.b); |