这里的测试表t1里只有一个字段id,以下语句执行后会将ID重复的去掉: 如 delete前: 1 1 3 delete后 1 3DELETE FROM T1 TT WHERE ROWID IN (SELECT RD FROM (SELECT ROWID RD, ID, LAG(ID) OVER(ORDER BY ID) PID FROM T1) T WHERE T.ID = PID);
如果你的表中,只有A、B两个字段那就没法用一条语句删除了,只能先用 insert into T_B select distinct * from MyTable ;然后再把原表全删了
能不能使用一个存储过程,先select distinct * from MyTable存到内存中,再把原表全部删了,再把内存中的记录插入到表中。存储过程应该怎么写,求指导。
能不能使用一个存储过程,先select distinct * from MyTable存到内存中,再把原表全部删了,再把内存中的记录插入到表中。存储过程应该怎么写,求指导。行倒是行 但是一般不建议这么干create or replace procedure pro_lwb_dd as cursor c_find is select distinct* from t; cc c_find%rowtype; begin open c_find; delete from t; loop fetch c_find into cc; exit when c_find%notfound; insert into t values(cc.字段1,cc.字段2); end loop; commit; close c_find; end;
能不能使用一个存储过程,先select distinct * from MyTable存到内存中,再把原表全部删了,再把内存中的记录插入到表中。存储过程应该怎么写,求指导。行倒是行 但是一般不建议这么干create or replace procedure pro_lwb_dd as cursor c_find is select distinct* from t; cc c_find%rowtype; begin open c_find; delete from t; loop fetch c_find into cc; exit when c_find%notfound; insert into t values(cc.字段1,cc.字段2); end loop; commit; close c_find; end;谢谢。这么做的缺点是什么?是数据大的时候很费事吗?
DELETE FROM MYTABLE T1 WHERE ROWID != (SELECT MAX(ROWID) FROM MYTABLE T2 WHERE T1.A = T2.A AND T1.B = T2.B)
什么,你的表没有主键?
如 delete前:
1
1
3
delete后
1
3DELETE FROM T1 TT
WHERE ROWID IN
(SELECT RD
FROM (SELECT ROWID RD, ID, LAG(ID) OVER(ORDER BY ID) PID FROM T1) T
WHERE T.ID = PID);
能不能使用一个存储过程,先select distinct * from MyTable存到内存中,再把原表全部删了,再把内存中的记录插入到表中。存储过程应该怎么写,求指导。
能不能使用一个存储过程,先select distinct * from MyTable存到内存中,再把原表全部删了,再把内存中的记录插入到表中。存储过程应该怎么写,求指导。行倒是行 但是一般不建议这么干create or replace procedure pro_lwb_dd as
cursor c_find is select distinct* from t;
cc c_find%rowtype;
begin
open c_find;
delete from t;
loop
fetch c_find into cc;
exit when c_find%notfound;
insert into t values(cc.字段1,cc.字段2);
end loop;
commit;
close c_find;
end;
能不能使用一个存储过程,先select distinct * from MyTable存到内存中,再把原表全部删了,再把内存中的记录插入到表中。存储过程应该怎么写,求指导。行倒是行 但是一般不建议这么干create or replace procedure pro_lwb_dd as
cursor c_find is select distinct* from t;
cc c_find%rowtype;
begin
open c_find;
delete from t;
loop
fetch c_find into cc;
exit when c_find%notfound;
insert into t values(cc.字段1,cc.字段2);
end loop;
commit;
close c_find;
end;谢谢。这么做的缺点是什么?是数据大的时候很费事吗?
WHERE ROWID != (SELECT MAX(ROWID)
FROM MYTABLE T2
WHERE T1.A = T2.A
AND T1.B = T2.B)