关于sql update set语句,效率的优化

鑫尼达

sql in Excel或者sql in access中,update 语句批量更新多条数据,怎么写才是最优的效率?

假如A表和B表结构相同,如下:

ID字段,数据字段1,数据字段2,数据字段N

其中,ID字段为主键(在access里有设置索引).数据字段有N列,N大于2小于20

假设现在,A表已有十万条数据;
B表有5万条数据,其中一万条是 A表没有的新的ID字段,两万条是A表已有但是至少有一个对应数据字段有变化的,还有两万条是A表有的但是所有数据字段都完全没有变化的.
现在要把B表新增的ID字段,有更新变化的ID字段,同步更新到A表,
sql语句要怎么写,才是效率最高的?

我目前的思路和写法:
第一步先把B表新增的插入到A表:
sql1="insert into A表 select B表.* from A表 left join B表 on A表.ID字段=B标.ID字段 where A表.ID字段 is null "

第二步,把B表的条目更新到A表
sql2=" update A表 inner join B表 on A表.ID字段=B表.ID字段 set A表.数据字段1=B表.数据字段1,A表.数据字段2=B表.数据字段2,A表.数据字段N=B表.数据字段N "
或者另一种写法:
sql3=" update A表 inner join B表 on A表.ID字段=B表.ID字段 set A表.数据字段1=B表.数据字段1,A表.数据字段2=B表.数据字段2,A表.数据字段N=B表.数据字段N where A表.数据字段1 <> B表.数据字段1 or A表.数据字段2 <> B表.数据字段2 or A表.数据字段N <> B表.数据字段N"

我的疑问:
1,有没有可能直接一句sql就搞定新增和更新? 前提是sql in Excel或者sql in access中.不是别的数据库软件.
2,第二步的两种写法哪种效率(主要是耗时更少)更高?
sql2相当于是直接把B表所有的都更新到A表,不管是否有变化,更新的条数应该是整个B表五万条.
sql3应该是,先where条件子句筛选出不相等的条目再更新到A表,更新的条数应该是两万条,但是我听说,where子句,用OR连接,会导致不走索引全表扫描而且毕竟要先做一个比较运算.那么sql2和sql3两种写法到底哪种效率更高呢?还有没有更优的写法呢?

amazeyeli

select into

amazeyeli

先delete再select into

鑫尼达

amazeyeli 发表于 2021-9-1 19:41
先delete再select into

感谢,我试试看,只知道insert into,还不知道还有个 select into

鑫尼达

这个问题,有没有大神帮忙看看,这种插入和更新的场景,要怎么写效率更高呀

zpy2

鑫尼达 发表于 2022-3-10 04:58
这个问题,有没有大神帮忙看看,这种插入和更新的场景,要怎么写效率更高呀

换sqlite,用 insert or replace语句

zpy2

zpy2 发表于 2022-3-10 08:24
换sqlite,用 insert or replace语句

https://www.jianshu.com/p/bfa617a0b9b1

鑫尼达

zpy2 发表于 2022-3-10 08:29
https://www.jianshu.com/p/bfa617a0b9b1

大神，那是不是意味着在access和Excel里没有更好的写法了啊？

WJBWJBWJB

最近也在研究这个，我的办法是先找出两表中重复的（ID字段为姓名），删除B表中重复的，再将A表（Excel）数据插入B表（Access）
就是不知道数据量大的话，效率怎么样。
Sub 表格不重复数据上传数据库()
Dim cnn, rs, SQL$, path$, mytable$
Set cnn = CreateObject("adodb.connection"): Set rs = CreateObject("Adodb.recordset")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.path & "\FTTP.accdb"
  mytable = ActiveSheet.Name '表名
  path = ThisWorkbook.FullName & "].[" & mytable & "$" & [a1].CurrentRegion.Address(0, 0)
  SQL = "select a.* from [Excel 12.0;Database=" & path & "] a," & mytable & " b where a.姓名=b.姓名"  '获取重复数据
  rs.Open SQL, cnn, 1, 3  'MsgBox "要删除重复数据有：" & rs.RecordCount & "条"
For x = 1 To rs.RecordCount
  If rs.EOF = False Then '不是最后一条时
    SQL = "delete  from " & mytable & " where 姓名='" & rs.Fields("姓名") & " '"
    cnn.Execute SQL '删除数据
    rs.MoveNext
  End If
Next x

SQL = "select * from [Excel 12.0;Database=" & path & "]"
SQL = "insert into " & mytable & " " & SQL
cnn.Execute SQL '插入数据
rs.Close: cnn.Close: Set rs = Nothing: Set cnn = Nothing
End Sub

WJBWJBWJB

别一样写法，原理一样
Sub 不重复数据上传()
Dim cnn, path$, mySht$, SQL$
Set cnn = CreateObject("ADODB.Connection")
path = ThisWorkbook.path & "\FTTP.accdb"    '指定数据库文件
mySht = ActiveSheet.Name
cnn.Open "Provider=Microsoft.Ace.OLEDB.16.0;Data Source=" & path '连接数据库
SQL = "Delete from " & mySht & " A where Exists(Select * from [Excel 12.0;Database=" & ThisWorkbook.FullName & "].[" & mySht & "$" & Range("a1").CurrentRegion.Address(0, 0) & "] where 姓名=A.姓名)"
cnn.Execute SQL '删除重复数据
SQL = "Insert Into " & mySht & " Select * from [Excel 12.0;Database=" & ThisWorkbook.FullName & ";].[" & mySht & "$" & Range("A1").CurrentRegion.Address(0, 0) & "]"
cnn.Execute SQL '插入数据
cnn.Close: Set cnn = Nothing
End Sub