数据添加(insert),删除(delete),更新(update)

1.将数据插入到相同结构不同的表中

   insert all when loc in ('NEW YORK','BOSTON') then

          into dept_east(deptno,dname,loc) values(deptno,dname,loc);

      when loc='CHICAGO' then

          into dept_mid(deptno,dname,loc) values(deptno,dname,loc);

      else

          into dept_west(deptno,dname,loc) values(deptno,dname,loc)

   select deptno,dname,loc from dept

 

2.用一个表的字段内容去更新另一张表的字段内容

      update (select e.sal as emp_sal,e.comm as emp_comm,ns.sal as ns_sal,

ns.sal/2 as ns_comm from emp e,new_sal ns

where e.deptno = ns.deptno)

 set emp_sal = ns_sal,emp_comm=ns_comm

 

3.根据条件从一个表中或更新或删除或插入到另一个表

   merge into emp_commssion ec

  using (select * from emp) emp on (ec.empno = emp.empno)

  when matched then

     update set ec.comm = 1000

     delete where (sal < 2000)

  where not matched then

     insert (ec.empno,ec.ename,ec.deptno,ec.comm) values(

              emp.empno,emp.ename,emp.deptno,emp.comm)

 

4.删除不存在部门的员工记录

     delete from emp where not exists(select * from dept where emp.deptno = dept.deptno)

 

5.删除重复记录

    delete from dupes where id not in(select min(id) from dupes group by name)

 

6.删除发生事故3次以上部门的所有员工

   delete from emp where deptno in (select deptno from dept_accidents group by deptno having count(*) >=3)

  7.保留相同id，成绩最大的一条记录（相同重复的夜删除）

delete from stu_score s where (s.score
not in (select max(y.score) from stu_score y group by y.id))
or (s.rowid > (select min(e.rowid) from stu_score e
where s.id = e.id))

8.把含有long raw类型的数据插入到分区表

create table tt (id int,content long);

declare
  for i in 1 .. 10000 loop
    insert into tt values(i,'Hello World');
  end loop;
  commit;
end;
    create table t_par(id int,content clob)
    partition by range(id)
    (partition t_part1 values less than (3000),
    partition t_part2 values less than (6000),
    partition t_part3 values less than (9000),
    partition t_part4 values less than (12000))
   
 alter table tt modify content clob;   
   insert into t_par(id,content) (select * from tt);