--MERGE的巧妙运用
/*
案例1:
需求为:将如下t记录的ID=1的NAME改为ID=2的NAME的值,把ID=2的NAME改为ID=1的NAME的值。
*/
drop table t;
create table t (id number,name varchar2(20));
insert into t values (1,'a');
insert into t values (2,'b');
COMMIT;
SQL> select * from t;
ID NAME
---------- --------------------
1 a
2 b
--如果执行如下:
UPDATE t SET NAME =(SELECT NAME FROM t WHERE ID=2) WHERE ID=1;这个时候ID=1的NAME值已改变了,就不可能用如下来更新了。
UPDATE t SET NAME =(SELECT NAME FROM t WHERE ID=1) WHERE ID=1;
--如果是过程就很简单了,可以把原先的值先存储起来。但是是否单条SQL一定不行呢?
--其实单条SQL是可以解决的,我们可灵活用MERGE搞定!构造出一个虚拟表T,然后再根据这个虚拟T表和真实的t表进行MERGE更新,就完成了。虚拟表类似如下:
SELECT 1 id,(SELECT name FROM t WHERE id=2) name FROM DUAL
UNION ALL
SELECT 2,(SELECT name FROM t WHERE id=1) FROM DUAL;
ID NAME
-----------
1 b
2 a
--有了这个思路,结合前面所学的MERGE知识,可以通过如下代码实现更新,代码相当简洁优雅。
MERGE INTO t
USING (SELECT 1 id,(SELECT name FROM t WHERE id=2) name FROM DUAL
UNION ALL
SELECT 2,(SELECT name FROM t WHERE id=1) FROM DUAL
) t_tmp
ON (t.id = t_tmp.id)
WHEN MATCHED THEN UPDATE set t.name = t_tmp.name;
SQL> select * from t;
ID NAME
---------- --------------------
1 b
2 a
--注:如果是9I固定需要INSERT,所以需要随便加上如下内容 WHEN NOT MATCHED THEN INSERT VALUES (1,'a')
--本案例用的是MERGE的方法,当然,其中的构造虚拟表也是个非常重要的思路,如果只查询出改变后的结果而无需更新,就可以不用MERGE,直接如下查出结果:
rollback;
WITH T AS
(SELECT 1 id,(SELECT name FROM t WHERE id=2) name FROM DUAL
UNION ALL
SELECT 2,(SELECT name FROM t WHERE id=1) FROM DUAL
)
select t.id,t.name from t ,t
where t.id=t.id;
ID NAME
--------
1 b
2 a
/*
案例2
通过MERGE,我们可以得到一个非常有用的思想,就是如下:只要能查出自己的改变后的结果,就可以更新到原来的表。用MERGE+ROWID方式。
在案例1中,我们还有另外一种思路来更新记录,不再采用构造出虚拟表,来关联t表,而是直接把真实结果用SELECT的方式取出,然后利用这个结果集更新回原表中。
*/
merge into t using
(
WITH t_tmp AS
(SELECT 1 id,(SELECT name FROM t WHERE id=2) name FROM DUAL
UNION ALL
SELECT 2,(SELECT name FROM t WHERE id=1) FROM DUAL
)
select t.id,t.rowid as rn ,t.name from t ,t_tmp
where t.id=t_tmp.id)n
on(t.rowid=n.rn)
when matched then update
set t.name=n.name;
SQL> select * from t;
ID NAME
----------
1 a
2 b
/*
注:直接UPDATE一个子查询的写法也可以,但是却有很多限制,稍微复杂的查询都不行。这时候用MERGE是最好的办法,结合ROWID的方式,
我们可快速准确的利用一个已经查询出来的结果集来更新自己,是一个非常好的思路的扩展,极其实用,希望给大家开发中带来帮助。
*/
/*
案例1:
需求为:将如下t记录的ID=1的NAME改为ID=2的NAME的值,把ID=2的NAME改为ID=1的NAME的值。
*/
drop table t;
create table t (id number,name varchar2(20));
insert into t values (1,'a');
insert into t values (2,'b');
COMMIT;
SQL> select * from t;
ID NAME
---------- --------------------
1 a
2 b
--如果执行如下:
UPDATE t SET NAME =(SELECT NAME FROM t WHERE ID=2) WHERE ID=1;这个时候ID=1的NAME值已改变了,就不可能用如下来更新了。
UPDATE t SET NAME =(SELECT NAME FROM t WHERE ID=1) WHERE ID=1;
--如果是过程就很简单了,可以把原先的值先存储起来。但是是否单条SQL一定不行呢?
--其实单条SQL是可以解决的,我们可灵活用MERGE搞定!构造出一个虚拟表T,然后再根据这个虚拟T表和真实的t表进行MERGE更新,就完成了。虚拟表类似如下:
SELECT 1 id,(SELECT name FROM t WHERE id=2) name FROM DUAL
UNION ALL
SELECT 2,(SELECT name FROM t WHERE id=1) FROM DUAL;
ID NAME
-----------
1 b
2 a
--有了这个思路,结合前面所学的MERGE知识,可以通过如下代码实现更新,代码相当简洁优雅。
MERGE INTO t
USING (SELECT 1 id,(SELECT name FROM t WHERE id=2) name FROM DUAL
UNION ALL
SELECT 2,(SELECT name FROM t WHERE id=1) FROM DUAL
) t_tmp
ON (t.id = t_tmp.id)
WHEN MATCHED THEN UPDATE set t.name = t_tmp.name;
SQL> select * from t;
ID NAME
---------- --------------------
1 b
2 a
--注:如果是9I固定需要INSERT,所以需要随便加上如下内容 WHEN NOT MATCHED THEN INSERT VALUES (1,'a')
--本案例用的是MERGE的方法,当然,其中的构造虚拟表也是个非常重要的思路,如果只查询出改变后的结果而无需更新,就可以不用MERGE,直接如下查出结果:
rollback;
WITH T AS
(SELECT 1 id,(SELECT name FROM t WHERE id=2) name FROM DUAL
UNION ALL
SELECT 2,(SELECT name FROM t WHERE id=1) FROM DUAL
)
select t.id,t.name from t ,t
where t.id=t.id;
ID NAME
--------
1 b
2 a
/*
案例2
通过MERGE,我们可以得到一个非常有用的思想,就是如下:只要能查出自己的改变后的结果,就可以更新到原来的表。用MERGE+ROWID方式。
在案例1中,我们还有另外一种思路来更新记录,不再采用构造出虚拟表,来关联t表,而是直接把真实结果用SELECT的方式取出,然后利用这个结果集更新回原表中。
*/
merge into t using
(
WITH t_tmp AS
(SELECT 1 id,(SELECT name FROM t WHERE id=2) name FROM DUAL
UNION ALL
SELECT 2,(SELECT name FROM t WHERE id=1) FROM DUAL
)
select t.id,t.rowid as rn ,t.name from t ,t_tmp
where t.id=t_tmp.id)n
on(t.rowid=n.rn)
when matched then update
set t.name=n.name;
SQL> select * from t;
ID NAME
----------
1 a
2 b
/*
注:直接UPDATE一个子查询的写法也可以,但是却有很多限制,稍微复杂的查询都不行。这时候用MERGE是最好的办法,结合ROWID的方式,
我们可快速准确的利用一个已经查询出来的结果集来更新自己,是一个非常好的思路的扩展,极其实用,希望给大家开发中带来帮助。
*/