深入SQL之merge into

===========================================================

深入SQL之merge into

===========================================================

在SQL語句中使用merge來實現插入與更新的合併操作，針對於從一個表插入或更新到另一個表有用
-- merge 用法
merge into sess_stats
using
(
select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = :sid
and (a.name like '%ga %'
or a.name like '%direct temp%')
) curr_stats
on (sess_stats.name = curr_stats.name)
when matched then
update set diff = curr_stats.value - sess_stats.value,
value = curr_stats.value
when not matched then
insert ( name, value, diff )
values
( curr_stats.name, curr_stats.value, null )
/

如果sess_stats、 curr_stats都是組合主鍵,即還有一個欄位add也是主鍵之一，那麼如下語句又該如何編寫呢？

on (sess_stats.name = curr_stats.name)

試圖改成：
on (sess_stats.name = curr_stats.name，sess_stats.add = curr_stats.add)

on (sess_stats.name = curr_stats.name and sess_stats.add = curr_stats.add)

lhpgh 評論於：2008.02.29 17:07