merge在Oracle中的用法注重问题：+ J3 H/ F( o0 S6 D& i
【样例】9 w1 T! j Q# J1 r4 r
MERGE INTO dest_table dest
4 v. l/ _" P, Q6 d% ?9 w USING source_table source
, e4 g% r- _4 ^( M3 [: R5 d ON (source.PENDINGCODE = dest.PENDINGCODE and source.PENDINGPERSONID = dest.PENDINGPERSONID)* |% o s6 ^+ Y9 L
WHEN MATCHED THEN8 d$ W9 x6 z) {3 V7 T
UPDATE SET dest.PENDINGID = source.PENDINGID
\ R# c0 `" N( @' s% N' ~7 ` WHEN NOT MATCHED THEN
9 O1 `1 u; G/ Z! V) ^' u INSERT& E& R% }9 j) e' k" k& E" Q
VALUES
) Q4 C& _1 A0 I a$ f- V/ N (source.PENDINGID, source.PENDINGCODE, source.PENDINGTITLE, source.PENDINGDATE, source.PENDINGSENDER, source.PENDINGPERSONID, source.PENDINGURL, source.PANDINGSTATUS, source.PANDINGCITYCODE, source.PANDINGTYPE, source.NOTE);
; z6 G9 W! x$ O# D 【注重问题】0 n8 t$ P* Y- m% `: d- K
1、前提：表dest_table和表source_table是同构的；& H! L, ?, ^. H
2、on前提中不能呈现primary key的属性列，而且保证on的前提在营业上讲具有独一性；3、update时，不能将primary key更新失踪，即只能以表的primary key作为update的set前提； |
发表于 2012-8-4 14:06:19
