日期:2014-05-16 浏览次数:21052 次
--创建表 create table TableA ( A_id number , name varchar2(1024) , uuid varchar2(36) , version varchar2(50), MMMM varchar2(40), NNNN varchar2(50), B_id number , --新加的字段,全部为空 primary key (A_id) ); --插入数据 insert into TableA(a_id, uuid, name, version) values(1, 'uid1', 'abc', 'version_1'); insert into TableA(a_id, uuid, name, version) values(2, 'uid1', 'abc', 'version_1'); insert into TableA(a_id, uuid, name, version) values(3, 'uid1', 'abc', 'version_1'); insert into TableA(a_id, uuid, name, version) values(4, 'uid1', 'abc', 'version_1'); insert into TableA(a_id, uuid, name, version) values(5, 'uid1', 'abc', 'version_1'); insert into TableA(a_id, uuid, name, version) values(6, 'uid2', 'abcd', 'version_2'); commit; --数据更新 merge into TableA a using (select t.*, rownum as b_id from (select uuid, version from TableA group by uuid, name, version) t) b on (a.uuid = b.uuid and a.version = b.version) when matched then update set a.b_id = b.b_id; commit; --数据查询 select * from TableA; A_ID NAME UUID VERSION MMMM NNNN B_ID ---------- -------------- ------------ -------------- -------------- -------------- ---------- 1 abc uid1 version_1 1 2 abc uid1 version_1 1 3 abc uid1 version_1 1 4 abc uid1 version_1 1 5 abc uid1 version_1 1 6 abcd uid2 version_2 2
------解决方案--------------------
由于没有数据库工具,大概写了下,可能有错误,只是个思路,你可以试试
create or replace procedure A as begin insert into TableB (B_id, name, uuid, version) select rownum as B_id, name, uuid, version from( select distinct d.name, d.uuid, d.version from TableA); update TableA set B_id = (select B_id from TableB where TableB.uuid = TableA.uuid and TableB.version = TableA.version); end;