17,086
社区成员
发帖
与我相关
我的任务
分享
SQL>
SQL> create table test(
2 name varchar(10), mob varchar(20), cardid varchar(30), crdate varchar(10));
Table created
SQL> begin
2 insert into test values('王大力','159000000','61033333333333','20130720');
3 insert into test values('王大力','159000000','61033333333333','20130722');
4 end;
5 /
PL/SQL procedure successfully completed
SQL> -- 删除
SQL> delete test
2 where exists(
3 select * from test t where test.name = t.name and test.crdate < t.crdate
4 );
1 row deleted
SQL> select * from test;
NAME MOB CARDID CRDATE
---------- -------------------- ------------------------------ ----------
王大力 159000000 61033333333333 20130722
SQL> -- 更新
SQL> merge into test t
2 using (select '王大力' name,
3 '159000000' mob,
4 '61033333333333' cardid,
5 '20130723' crdate from dual) m
6 on(t.name = m.name)
7 when matched then
8 update set crdate = m.crdate
9 when not matched then
10 insert (name , mob , cardid , crdate) values(m.name , m.mob , m.cardid , m.crdate);
1 row merged
SQL> select * from test;
NAME MOB CARDID CRDATE
---------- -------------------- ------------------------------ ----------
王大力 159000000 61033333333333 20130723
SQL> drop table test purge;
Table dropped
SQL>