delete多条数据解决方法

delete多条数据
表a(datetime date primary key)
表b(字段1 varchar2(20) primary key,
datetime date,
bigfiled blob)
假设表a有八万表记录
表b有24万条记录
现在我想实现的delete表a的datetime字段值在表b中不存在的记录。必须考虑到效率的问题。
[最优解释]
-- 哦，不存在，看错了! 用NOT EXISTS

-- a、b表的datetime字段创建索引

CREATE index on a(datetime asc);
CREATE index on b(datetime asc);

DELETE FROM b u1
WHERE NOT EXISTS ( SELECT DISTINCT u2.datetime FROM a u2
               WHERE u2.datetime=u1.datetime );

[其他解释]


delete from a where not exists(select 1 from b where a.datetime=b.datetime)

[其他解释]

-- a、b表的datetime字段创建索引
CREATE index on a(datetime asc);
CREATE index on b(datetime asc);

DELETE FROM a u1
WHERE EXISTS ( SELECT DISTINCT u2.datetime FROM b u2
               WHERE u2.date=u1.date );

[其他解释]

引用:

SQL code
-- a、b表的datetime字段创建索引
CREATE index on a(datetime asc);
CREATE index on b(datetime asc);

DELETE FROM a u1
WHERE EXISTS ( SELECT DISTINCT u2.datetime FROM b u2
WHERE u2.……

谢谢。是我搞错了，是删除表b中的记录，条件是表b的datetime在表a中不存在。
[其他解释]

引用:

引用:
SQL code
-- a、b表的datetime字段创建索引
CREATE index on a(datetime asc);
CREATE index on b(datetime asc);

DELETE FROM a u1
WHERE EXISTS ( SELECT DISTINCT u2.datetime FROM b u2
W……

-- 就算搞错了，还有必要问题吗？不是一样：表a、表b调个个儿就OK了？
[其他解释]

引用:

引用:
SQL code
-- a、b表的datetime字段创建索引
CREATE index on a(datetime asc);
CREATE index on b(datetime asc);

DELETE FROM a u1
WHERE EXISTS ( SELECT DISTINCT u2.datetime FROM b u2
W……

-- 如果这个问题还要问的话，我真得怀疑你的智商啦!
[其他解释]

引用:

SQL code
-- a、b表的datetime字段创建索引
CREATE index on a(datetime asc);
CREATE index on b(datetime asc);

DELETE FROM a u1
WHERE EXISTS ( SELECT DISTINCT u2.datetime FROM b u2
WHERE u2.……

这是删除存在的数据吧


DELETE FROM a u1
WHERE EXISTS ( SELECT DISTINCT u2.datetime FROM b u2
               WHERE u2.date(+)=u1.date and u2.date is null);

[其他解释]

引用:

引用:
SQL code
-- a、b表的datetime字段创建索引
CREATE index on a(datetime asc);
CREATE index on b(datetime asc);

DELETE FROM a u1
WHERE EXISTS ( SELECT DISTINCT u2.datetime FROM b u2
W……

-- 你不是说删除b表吗？怎么又这样啦？

[其他解释]

引用:

引用:
SQL code
-- a、b表的datetime字段创建索引
CREATE index on a(datetime asc);
CREATE index on b(datetime asc);

DELETE FROM a u1
WHERE EXISTS ( SELECT DISTINCT u2.datetime FROM b u2
W……

-- a、b表的datetime字段创建索引
CREATE index on a(datetime asc);
CREATE index on b(datetime asc);

DELETE FROM b u1
WHERE EXISTS ( SELECT DISTINCT u2.datetime FROM a u2
               WHERE u2.datetime=u1.datetime );

[其他解释]
drop table a;
create table a as select distinct datetime as datetime from b;
alter table b add primary key a_pk on a(datetime);
[其他解释]

学习了！

delete多条数据解决方法

热点推荐