Prayer

在一般中寻求卓越
posts - 1256, comments - 190, trackbacks - 0, articles - 0
  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

DB2 Merge语句

Posted on 2011-10-18 15:42 Prayer 阅读(654) 评论(0)  编辑 收藏 引用 所属分类: 数据库,SQLDB2
DB2 Merge语句是经常可以用到的DB2语句,下面对DB2 Merge语句作了详细的介绍,如果您对此方面感兴趣的话,不妨一看。

  DB2 Merge语句的作用非常强大,它可以将一个表中的数据合并到另一个表中,在合并的同时可以进行插入、删除、更新等操作。我们还是先来看个简单的例子吧,假设你定义了一个雇员表(employe),一个经理表(manager),如下所示:

  ---雇员表(EMPLOYE)

以下是代码片段:
    CREATE TABLE EMPLOYE (
  EMPLOYEID INTEGER NOT NULL,---员工号
  NAME VARCHAR(20) NOT NULL,---姓名
  SALARY DOUBLE---薪水
  );
  INSERT INTO EMPLOYE (EMPLOYEID,NAME,SALARY) VALUES
  (1,'张三',1000),
  (2,'李四',2000),
  (3,'王五',3000),
  (4,'赵六',4000),
  (5,'高七',5000);

  --经理表(MANAGER)

以下是代码片段:
    CREATE TABLE MANAGER (
  EMPLOYEID INTEGER NOT NULL,---经理号
  NAME VARCHAR(20) NOT NULL,---姓名
  SALARY DOUBLE---薪水
  );
  INSERT INTO MANAGER (MANAGERID,NAME,SALARY) VALUES
  (3,'王五',5000),
  (4,'赵六',6000);

  ---雇员表(EMPLOYE)

以下是代码片段:
    CREATE TABLE EMPLOYE (
  EMPLOYEID INTEGER NOT NULL,---员工号
  NAME VARCHAR(20) NOT NULL,---姓名
  SALARY DOUBLE---薪水
  );
  INSERT INTO EMPLOYE (EMPLOYEID,NAME,SALARY) VALUES
  (1,'张三',1000),
  (2,'李四',2000),
  (3,'王五',3000),
  (4,'赵六',4000),
  (5,'高七',5000);

  --经理表(MANAGER)

以下是代码片段:
    CREATE TABLE MANAGER (
  EMPLOYEID INTEGER NOT NULL,---经理号
  NAME VARCHAR(20) NOT NULL,---姓名
  SALARY DOUBLE---薪水
  );
  INSERT INTO MANAGER (MANAGERID,NAME,SALARY) VALUES
  (3,'王五',5000),
  (4,'赵六',6000);

 经过一段时间,你发现这样的数据模型,或者说表结构设计简直就是一大败笔,经理和雇员都是员工嘛,为什么要设计两个表呢?发现错误后就需要改正,所以你决定,删除经理表(MANAGER)表,将MANAGER 表中的数据合并到EMPLOYE 表中,仔细分析发现,王五在两个表中都存在(可能是干的好升官了),而刘八在EMPLOYE 表中并不存在,现在,我们要求把EMPLOYE 表中不存在的MANAGER都插入到EMPLOYE 表中,存在的更新薪水。该怎么办呢?这个问题并不难,通常,我们可以分两步,如下所示:

  --更新存在的

以下是代码片段:
    UPDATE EMPLOYE AS EM SET SALARY=(SELECT SALARY FROM MANAGER WHERE MANAGERID=EM.EMPLOYEID)
  WHERE EMPLOYEID IN (
  SELECT MANAGERID FROM MANAGER
  );

  ---插入不存在的

以下是代码片段:
    INSERT INTO EMPLOYE (EMPLOYEID,NAME,SALARY)
  SELECT MANAGERID,NAME,SALARY FROM MANAGER WHERE MANAGERID NOT IN (
  SELECT EMPLOYEID FROM EMPLOYE
  );

  --更新存在的

以下是代码片段:
    UPDATE EMPLOYE AS EM SET SALARY=(SELECT SALARY FROM MANAGER WHERE MANAGERID=EM.EMPLOYEID)
  WHERE EMPLOYEID IN (
  SELECT MANAGERID FROM MANAGER
  );

  ---插入不存在的

以下是代码片段:
    INSERT INTO EMPLOYE (EMPLOYEID,NAME,SALARY)
  SELECT MANAGERID,NAME,SALARY FROM MANAGER WHERE MANAGERID NOT IN (
  SELECT EMPLOYEID FROM EMPLOYE
  );

  上面的处理是可以的,但是我们还可以有更简单的方法,就是用Merge语句,如下所示:

以下是代码片段:
    MERGE INTO EMPLOYE AS EM
  USING MANAGER AS MA
  ON EM.EMPLOYEID=MA.MANAGERID
  WHEN MATCHED THEN UPDATE SET EM.SALARY=MA.SALARY
  WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY);
  MERGE INTO EMPLOYE AS EM
  USING MANAGER AS MA
  ON EM.EMPLOYEID=MA.MANAGERID
  WHEN MATCHED THEN UPDATE SET EM.SALARY=MA.SALARY
  WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY);

 


 在上面的处理中,我们用经理表(MANAGER)的薪水更新了雇员表(EMPLOYE)的薪水,假设现在要求,如果经理表(MANAGER)的薪水>雇员表(EMPLOYE)的薪水的时候更新,否则不更新,怎么办呢?如下:

以下是代码片段:
    MERGE INTO EMPLOYE AS EM
  USING MANAGER AS MA
  ON EM.EMPLOYEID=MA.MANAGERID
  WHEN MATCHED AND EM.SALARY
  WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY);
  MERGE INTO EMPLOYE AS EM
  USING MANAGER AS MA
  ON EM.EMPLOYEID=MA.MANAGERID
  WHEN MATCHED AND EM.SALARY
  WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY);

  不仔细的朋友可能没有看出上面两条语句的区别,哈哈,请仔细对比一下这两条语句。上面的语句中多了ELSE IGNORE语句,它的意思正如它英文的意思,其它情况忽略不处理。如果你认为理论上应该不存在EM.SALARY>MA.SALARY的数据,如果有,说明有问题,你想抛个异常,怎么办?如下:

以下是代码片段:
    MERGE INTO EMPLOYE AS EM
  USING MANAGER AS MA
  ON EM.EMPLOYEID=MA.MANAGERID
  WHEN MATCHED AND EM.SALARY
  WHEN MATCHED AND EM.SALARY>MA.SALARY THEN SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'EM.SALARY>MA.SALARY'
  WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY)
  ELSE IGNORE;
  MERGE INTO EMPLOYE AS EM
  USING MANAGER AS MA
  ON EM.EMPLOYEID=MA.MANAGERID
  WHEN MATCHED AND EM.SALARY
  WHEN MATCHED AND EM.SALARY>MA.SALARY THEN SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'EM.SALARY>MA.SALARY'
  WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY)
  ELSE IGNORE;

  对于EM.SALARY>MA.SALARY的情况,如果你不想抛异常,而是删除EMPLOYE中的数据,怎么办?如下:

以下是代码片段:
    MERGE INTO EMPLOYE AS EM
  USING MANAGER AS MA
  ON EM.EMPLOYEID=MA.MANAGERID
  WHEN MATCHED AND EM.SALARY
  WHEN MATCHED AND EM.SALARY>MA.SALARY THEN DELETE
  WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY)
  ELSE IGNORE;
  MERGE INTO EMPLOYE AS EM
  USING MANAGER AS MA
  ON EM.EMPLOYEID=MA.MANAGERID
  WHEN MATCHED AND EM.SALARY
  WHEN MATCHED AND EM.SALARY>MA.SALARY THEN DELETE
  WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY)
  ELSE IGNORE;

  以上简单介绍了Merge语句的使用,它的应用不只是上面介绍的情况,其实它可以应用在很多其他语句不好处理情况,这需要你去发现,记住熟能生巧




只有注册用户登录后才能发表评论。
【推荐】超50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理