如何从一个表中删除与另一个表相同的记录.txt

VB技巧问答10000例 VB技巧问答10000例

    1、select a.* into tmptable from a,b where a.c=b.c and a.d=b.d and a.e=b.e 
    2、drop table a 
    3、select * into a from tmptable 
    4、drop table tmptable 
    我在ms sql上调试通过，速度还行。你不妨试一下！ 
<END>     
1.首先打开a表于rs1,将d,c,e数据分别存入变量a,b,c中 
     应用select语句，结果储存在变量rs2，执行: 
     "select * from b where d=" + a + " and c=" + b + " and e=" + c 
     （这里 * 代表搜索所有字段，b 代表 表b,“+”是连字符，连接之后应得到sql语句"select * from b where d=1 and c=2 and e=2.3"） 
     然后在判断rs2是否为eof，如果是，表明在b表有这行数据，就运行删除语句： 
     "delete from a where d=" + a + " and c=" + b + " and e=" + c 
     "delete from b where d=" + a + " and c=" + b + " and e=" + c 
     如果否，则跳过。然后重复第一步，让rs1.movenext 
     以下是本人摘写的一段Visual Basic 6 的源代码。 
     
    Private Sub DeleteSame() 
     
    Dim db As Database 
    Set db = OpenDatabase("c:\文件名.mdb", True, False, "pwd=;") '打开数据库 
    Dim rs1 As Recordset 
    Dim rs2 As Recordset 
    Dim a, b, c 
    Set rs1 = db.OpenRecordset("a") '打开表a 
     
    While Not rs1.EOF '开始循环 
    a = rs1("d") '将d,c,e字段存入a,b,c 
    b = rs1("c") 
    c = rs1("e") 
    Set rs2 = db.OpenRecordset("Select * from b where d=" + a + " and c=" + b + " and e=" + c) '用select语句查找 
    If Not rs2.EOF Then '判断rs2是否eof。如果是，则执行下列语句（End If之前） 
    db.Execute ("delete form a where d=" + a + " and c=" + b + " and e=" + c) 
    db.Execute ("delete form b where d=" + a + " and c=" + b + " and e=" + c) 
    End If 
    rs1.MoveNext '移动到下一条 
    Wend 
     
    End Sub 
<END>    
可以用：Delete from A 
     where A.d in (select d from B ) 
     and A.c in (select c from B ) 
    如果字段C和D是字符的话也可以用 
     Delete from A 
     where A.d+A.C in (select d+C from B ) 
<END>     
我们先假定表a和表b在一个库中。 
    可以使用如下sql语句： 
     delect * from a where exists ( select * from b where d=a.d and e=a.e and c=a.c) 
<END>