不懂T1表 T2表 ID Path ID Path 1 abc 1 abc 2 bcd 2 bcd 3 cde 3 cde 4 efg 4 sds就这样 select * from T1,T2 where T1.Path=T2.Path后 T1,T2表应该是这样 ID Path ID Path 4 efg 4 sds
可能我表达的不明确,我的意思是删除T1,T2表中path字段内容相同的记录
那能不能换种方式来说,能不能查出每张表的多余记录或缺少记录 因为查两张表中相同的记录很容易,而且速度也较块 所以能不能先 select t1.path,t2,path from t1,t2 where t1.path=t2.path 这样返回两张表相同的记录能不能根据这个查出每张表中缺少或多余的记录呢?
delete t1.path,t2.path from t1,t2 where t1.path=t2.path 不知道行不行。
DELETE T1 WHERE ID IN (SELECT T1.ID FROM T1, T2 WHERE T1.Path = T2.Path) DELETE T2 WHERE ID IN (SELECT T2.ID FROM T1, T2 WHERE T1.Path = T2.Path)
楼上的,执行完第一句后第二句还能正确执行么? 还是建议用临时表,建一个只有path字段的表temp insert into temp select t1.path from T1,T2 where T1.Path=T2.Path 然后 DELETE from T1 WHERE path IN (select path from temp) DELETE from T2 WHERE path IN (select path from temp)
呵呵...还是建议用临时表,建一个只有path字段的表temp insert into temp select t1.path from T1,T2 where T1.Path=T2.Path 然后 DELETE from T1 WHERE path IN (select path from temp) DELETE from T2 WHERE path IN (select path from temp)drop table temp//删除临时表
begin tran select a.path into #tmp from t1 a,t2 b where a.path=b.path delete from t1 where path in(select path from #tmp) delete from t2 where path in(select path from #tmp) drop table #tmp commit tran
ID Path ID Path
1 abc 1 abc
2 bcd 2 bcd
3 cde 3 cde
4 efg 4 sds就这样
select * from T1,T2 where T1.Path=T2.Path后
T1,T2表应该是这样
ID Path ID Path
4 efg 4 sds
因为查两张表中相同的记录很容易,而且速度也较块
所以能不能先 select t1.path,t2,path from t1,t2 where t1.path=t2.path
这样返回两张表相同的记录能不能根据这个查出每张表中缺少或多余的记录呢?
不知道行不行。
DELETE T2 WHERE ID IN (SELECT T2.ID FROM T1, T2 WHERE T1.Path = T2.Path)
还是建议用临时表,建一个只有path字段的表temp
insert into temp select t1.path from T1,T2 where T1.Path=T2.Path
然后
DELETE from T1 WHERE path IN (select path from temp)
DELETE from T2 WHERE path IN (select path from temp)
insert into temp select t1.path from T1,T2 where T1.Path=T2.Path
然后
DELETE from T1 WHERE path IN (select path from temp)
DELETE from T2 WHERE path IN (select path from temp)drop table temp//删除临时表
select a.path into #tmp from t1 a,t2 b where a.path=b.path
delete from t1 where path in(select path from #tmp)
delete from t2 where path in(select path from #tmp)
drop table #tmp
commit tran