AGWJH ASJ AUTID AZBS BGWJH BSJ BUTID BZBS
1 4 200812 50150000000000 001001
2 2 200812 50150000000000 001001
3 2 200809 50150000000000 001001
4 2 200808 50150000000000 001001
5 2 200807 50150000000000 001001
6 2 200806 50150000000000 001001
7 2 200805 50150000000000 001001
8 2 200804 50150000000000 001001
9 2 200802 50150000000000 001001
10 2 200801 50150000000000 001001
从上表查询如下数据:
条件是:agwjh不为空和为空比较,如果有asj=bsj and autid=butid and azbs=bzbs时,去掉agwjh为空的那条数据。
1 4 200812 50150000000000 001001
2 2 200812 50150000000000 001001
3 2 200809 50150000000000 001001
4 2 200808 50150000000000 001001
5 2 200807 50150000000000 001001
6 2 200806 50150000000000 001001
7 2 200805 50150000000000 001001
8 2 200804 50150000000000 001001
9 2 200802 50150000000000 001001
10 2 200801 50150000000000 001001
从上表查询如下数据:
条件是:agwjh不为空和为空比较,如果有asj=bsj and autid=butid and azbs=bzbs时,去掉agwjh为空的那条数据。
1 4 200812 50150000000000 001001
2 2 200812 50150000000000 001001
3 2 200809 50150000000000 001001
4 2 200808 50150000000000 001001 从上表查询如下数据:
条件是:agwjh不为空和为空比较,如果有asj=bsj and autid=butid and azbs=bzbs时,去掉agwjh为空的那条数据。
delete from table t where t.rowid in(
select t.rowid from table m,table n
where m.asj = n.bsj
and m.autid = n.butid
and azbs = n.bzbs
and n.agwjh is null);
update Your_table set agwjh=ltrim(agwjh);
这样写selectSELECT m.*
FROM TABLE m, TABLE n
WHERE m.asj = n.bsj
AND m.autid = n.butid
AND azbs = n.bzbs
AND m.agwhj IS NOT NULL
AND n.agwjh IS NOT NULL;
猜测为:delete from tb where asj=bsj and autid=butid and azbs=bzbs and AGWJH = ''
delete from Your_table
where asj = bsj
and autid = butid
and azbs = bzbs
and length(agwjh) > length(ltrim(agwjh));
delete from Your_table
where asj = bsj
and autid = butid
and azbs = bzbs
and (agwjh = '' or agwjh is null);
where asj = bsj
and autid = butid
and azbs = bzbs
and agwjh is null
select n.rowid from table m,table n --这里测试取n表 是因为和后面的条件n.agwjh保持一致
where m.asj = n.bsj
and m.autid = n.butid
and m.azbs = n.bzbs
and n.agwjh is null);
上面的sql会删除这样的记录 如果有2条记录一样的 但同时他们的agwjh也为空 不知道这种是否要删除
如果不删除就用下面这种
delete from table t where t.rowid in(
select n.rowid from table m,table n --这里测试取n表 是因为和后面的条件n.agwjh保持一致
where m.asj = n.bsj
and m.autid = n.butid
and m.azbs = n.bzbs
and n.agwjh is null
and m.agwjh is not null);