两个表字段相同,将tmp表中数据更新至data表中,然后清空tmp表
按照以下代码更新,会出现部分data表中没有的数据被误删除(即没有导入)
Cmd.CommandText = "select * from tmp where name in (select name from data) and account in (select account from data) and subject in (select subject from data) and iounumber in (select iounumber from data)"
Dr = Cmd.ExecuteReader(CommandBehavior.Default)
If Dr.HasRows = True Then
While Dr.Read
B = CDbl(Dr.Item("贷").ToString())
TA = Dr.Item("account")
TS = Dr.Item("subject")
TC = Dr.Item("iounumber")
C = Dr.Item("name")
Cmd.Connection = Oledb2
Cmd.CommandText = "update data set 贷" & V & "=convert(money,'" & (B) & "') where name='" & (C) & "' and account='" & (TA) & "' and subject='" & (TS) & "' and iounumber='" & (TC) & "'"
Cmd.ExecuteNonQuery()
End While
End If
Dr.Close()
Oledb2.Close()
Cmd.Connection = Oledb1
Cmd.CommandText = "delete from tmp where name in (select name from data) and account in (select account from data) and subject in (select subject from data) and iounumber in (select iounumber from data)"
Cmd.ExecuteNonQuery()
Cmd.CommandText = "insert into data(type,subject,account,name,[currency],IouNumber,贷" & V & ") select type,subject,account,name,[currency],IouNumber,贷 from tmp"
Cmd.ExecuteNonQuery()
问题应该出在delete那句上,但是上面update却没有问题,很奇怪!
按照以下代码更新,会出现部分data表中没有的数据被误删除(即没有导入)
Cmd.CommandText = "select * from tmp where name in (select name from data) and account in (select account from data) and subject in (select subject from data) and iounumber in (select iounumber from data)"
Dr = Cmd.ExecuteReader(CommandBehavior.Default)
If Dr.HasRows = True Then
While Dr.Read
B = CDbl(Dr.Item("贷").ToString())
TA = Dr.Item("account")
TS = Dr.Item("subject")
TC = Dr.Item("iounumber")
C = Dr.Item("name")
Cmd.Connection = Oledb2
Cmd.CommandText = "update data set 贷" & V & "=convert(money,'" & (B) & "') where name='" & (C) & "' and account='" & (TA) & "' and subject='" & (TS) & "' and iounumber='" & (TC) & "'"
Cmd.ExecuteNonQuery()
End While
End If
Dr.Close()
Oledb2.Close()
Cmd.Connection = Oledb1
Cmd.CommandText = "delete from tmp where name in (select name from data) and account in (select account from data) and subject in (select subject from data) and iounumber in (select iounumber from data)"
Cmd.ExecuteNonQuery()
Cmd.CommandText = "insert into data(type,subject,account,name,[currency],IouNumber,贷" & V & ") select type,subject,account,name,[currency],IouNumber,贷 from tmp"
Cmd.ExecuteNonQuery()
问题应该出在delete那句上,但是上面update却没有问题,很奇怪!
这一句你是要这种效果吗?
name,account,subject,iounumber四项都相符才更新“贷”
但iounumber并不是每条数据都有,前面三项每条数据都有
但四项核对一定只有一条记录
select * from tmp where name in (select isnull(name,'') from data) and account in (select isnull(account,0) from data) and subject in (select isnull(subject,'') from data) and iounumber in (select isnull(iounumber,0) from data)
超时已过期
update data from tmp
set
where 。首先在数据库中先测试下自己的sql语句
但是很容易出错,因为记录可能的非唯一性,字段的转换等
用表连接吧! where 或者 inner join
刚才查了查inner join
是不是会产生一个新的查询集?查询集怎么更新进表里?
set a.a = b.b,a.c = c.c,a.d = c.d
from b,c,d
where a.bid = b.id
and a.cid = b.id
and a.did = d.id
and ......
subject name account iounumber 贷
123456 123456 1111111 77 111111
如果data中有了一条subject,name,account相同,但iounumber不同的记录
那就有可能不导入,这个很郁闷啊!
我总觉得问题出在那句delete上,因为当数据库为空时,导入一天的数据就是对的
一旦数据库里已有一天数据的情况下,再做导入就会发生上面的情况
大部分的iounumber都是空值
应该是第一个 贷 后边的 " & V & " 这里,不知道具体是什么意思!如果有多个贷的列,后边有跟区别的字符,那么V应该在这些区别字符范围内,如果超过会插入不成功。
因为我把清空数据库这句话删除,运行完后查看tmp,结果是空表
from tmp a,data b
where a.[name] = b.[name] and a.account = b.account
and a.subject = b.subject and a.iounumber = b.iounumber这个筛选的数据可能和你原来筛选出来的不同,具体怎么删除数据给些表的数据,两表的,然后说明下删除的条件!
delete from tmp from tmp inner join data on tmp.name=data.name and ......
又学到了很多东西啊,谢谢LS的诸位了!20楼的广告去死吧