db2中有类似与oracle的merge into的东东?

[wqq0712]

请问:db2中有类似与oracle的merge into的东东?
A B 2个库中有2张相同结构的表..
我想实现让A表中的记录移到B表的效果..
就是如果B表中已经存在跟A表中主键一样的记录(主键一致)就执行更新B表!
如果不存在,就取A表中记录值插到B表中..



顺便谁有的DB文档（类似于SQLSERVER那样的联机帮助）给份!!THANKS！

类似以下的解决方法就不要发了..先不说效率..当复制粘贴就能把我搞崩溃了..
update table1 set
f1=(select f1 from table1 where fid='b'),
f2=(select f2 from table1 where fid='b'),
f3=(select f3 from table1 where fid='b'),
where fid='a'

[Mr_Bean]

export import 命令可以

[wqq0712]

谢谢楼上大哥哈!不过DB2我不熟..
export import命令怎么个用法..能举个例子不..??

[Mr_Bean]

? export
? import

db2信息中心

[wgqqgw]

要看你的DB2版本，V9是一定有MERGE的（我正在用），其他版本就不确定了，查一下SQL REFERENCE就知道啦。

MERGE INTO TARGET_TABLE
USING SOURCE_TABLE
WHEN MATCHED THEN
UPDATE ()=()
WHEN NOT MATCHED THEN
INSERT () VALUES ()

如果你的DB2没有MERGE，那么就试试用export+import咯，import的REPLACE关键字可以覆盖掉已有记录，小心点用，先用少数据量在测试服务器试试效果是否符合你要求。

[yangxiao_jiang]

V9以下的是没有MERGE的，你用export把不一样的到出来，export可以根据sql到出，然后用import把到出的，到入到数据库中就行了。

[littleboys]

方法1： 使用export import
格式
export to table_a.ixf of ixf SELECT * FROM table_a fetch first 10 rows only

IMPORT FROM filename OF {IXF | ASC | DEL | WSF} INSERT | INSERT_UPDATE | REPLACE | REPLACE_CREATE | CREATE into table_b

例子：

db2 create table t_import (id int not null primary key,name varchar(20),age int default 0)
db2 create table t_import2 (id int not null primary key,name varchar(20),age int default 0)


db2 insert into t_import values(1,'aaa',11),(2,'bbb',12),(3,'ccc',13)@

db2 export to d:/db2/sql/t_import.ixf of ixf select * from t_import

db2 import from d:/db2/sql/t_import.ixf of ixf INSERT_UPDATE into t_import2
db2 select * from t_import2
db2 update t_import2 set name='abc',age =111 where id=2
db2 select * from t_import2
db2 import from d:/db2/sql/t_import.ixf of ixf INSERT_UPDATE into t_import2
db2 select * from t_import2

方法2
当然也可以先删除B 表中存在A 表中相同数据，然后插入就可以了

delete from t_import2 where id in(select id from t_import)

insert into t_import2 select * from t_import

方法2 更方便

[meiZiNick]

我也想了解，谢谢LZ.

[xiyuan1999]

可以，参看DB2文档： http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010873.htm

Examples
Example 1: For activities whose description has been changed, update the description in the archive table. For new activities, insert into the archive table. The archive and activities table both have activity as a primary key.
MERGE INTO archive ar
USING (SELECT activity, description FROM activities) ac
ON (ar.activity = ac.activity)
WHEN MATCHED THEN
UPDATE SET
description = ac.description
WHEN NOT MATCHED THEN
INSERT
(activity, description)
VALUES (ac.activity, ac.description)

[zhaojianmi1]

分两步，delete，insert
或者export，然后import

[zhaojianmi1]

也可以update，insert

[JH5221314]

顶一个！