select e_code from emp as a , (select e_code from emp by e_code having count(*)>1) b where a.e_code =b.e_code 这样查出来重复的记录后,怎样用存储过程修改掉重复的e_code呢?
不需要存储过程,去查查row_number分析函数
楼上的是最简单的,ROW_NUMBER,按照员工号分组,改变部分即可,对系统影响最小
--用rowid delete emp a where a.rowid!=(select max(rowid) from emp b where a.e_code=b.e_code);
刚看懂了row_number()函数的用法with emp as (select 1 as e_code,'Jack' as e_name from dual union all select 1,'Lucy' from dual union all select 2,'Nick' from dual union all select 3,'Macle' from dual union all select 2,'Toy' from dual) select e_code,e_name,row_number() over (partition by e_code order by e_code) from emp;
1.disable掉所有外键约束
2.把e_code的序列值存入变量var
3.修改子表的外键字段的值为var
4.修改主表的e_code字段的值为var
5.enable所有外键约束
0.找出需要修改e_code的记录集
1.把e_code的序列值存入变量var
2.修改主表的e_code字段的值为var
from emp as a ,
(select e_code from emp by e_code having count(*)>1) b
where a.e_code =b.e_code
这样查出来重复的记录后,怎样用存储过程修改掉重复的e_code呢?
delete emp a where a.rowid!=(select max(rowid) from emp b where a.e_code=b.e_code);
(select 1 as e_code,'Jack' as e_name from dual
union all
select 1,'Lucy' from dual
union all
select 2,'Nick' from dual
union all
select 3,'Macle' from dual
union all
select 2,'Toy' from dual)
select e_code,e_name,row_number() over (partition by e_code order by e_code) from emp;