使用另一个表中的特定信息更新表 [英] Updating a table with specific info from another table
问题描述
我有一个联系人表格，其中包含姓名地址等，但在电话号码字段中缺少所有电话
号码。我刚刚在Excel中收到了一个更新的
客户列表并已导入到新表中。
我可以运行只接受电话号码的查询从新表
并将旧表的电话号码字段从空更新到正确的
信息？
嗨John，
在sql Server中你可以像这样运行Transact sql语句：
strSql ="更新t1设置t1.Phone = t2.Phone from mainTbl t1 Join
ImportTbl t2 on t1.IDnum = t2.IDnum"
更新表格一次拍摄。
但是我无法在Access中使用它 - 它必须是一个限制
的Access Jet Sql。如果有人知道如何在Access中使这种sql
语句工作 - 请分享。
Access的替代方法是使用DAO与sql和
循环导入表：
---------------------- -------------------------
Dim DB作为DAO数据库，RS作为DAO.RecordSet
Dim strsql As String，strPhone As String，intID As Integer
设置DB = CurrentDB
设置RS = DB.OpenRecordset（" ImportTable"）
请不要RS.EOF
strPhone = RS！电话
intID = RS！IDfield
strSql = 更新mainTbl Set Phone =''" &安培; strPhone _
& "'''IDnum =" &安培; intID
DoCmd.RunSql strSql
RS.MoveNext
循环
--------- ------------------------------------------
此方法一次只更新一行，但它可以工作。
Rich
***通过Developersdex发送< a rel =nofollowhref =http://www.developersdex.comtarget =_ blank> http://www.developersdex.com ***
< br>
" Rich P"写了
在sql Server中你可以像这样运行一个Transact
sql语句：
strSql ="更新t1设置t1.Phone = t2.Phone来自mainTbl t1加入
ImportTbl t2 on t1.IDnum = t2.IDnum"
to一次更新一个表。
但是我无法在Access中使用它 -
它必须是Access Jet Sql的限制。如果
有人知道如何在Access中制作这种sql
语句 - 请分享。
我很惊讶有多少人似乎认为如果某些东西在他们最喜欢的软件中工作，它应该在其他软件中不变 -
特别是那些能够在服务器数据库中创建工作SQL的人。
在Access中，您使用查询生成器：将每个表添加为一个数据
源，将它们加入公共唯一标识字段，拖动
必填字段，单击菜单中的Query，选择Update，然后设置
将原始表格中的电话号码更新为新表格中的
电话号码。
如果你真的感觉不得不看看Jet SQL，你可以点击工具栏上最左边的
图标或选择View |设计视图菜单中的SQL
查询生成器中的
。
Larry Linson
Microsoft Access MVP
>>
我很惊讶有多少人似乎认为如果有什么东西在他们最喜欢的软件中工作，它应该有效在其他软件中没有改变 -
特别是足够智能的人在服务器中创建可用的SQL
DB。
...
Larry Linson
Microsoft Access MVP
<<
您和其他任何人都没有演示如何做一个这样的Tsql
语句
----------------------- ------------------------------
更新t1从mainTbl设置t1.Phone = t2.Phone t1加入ImportTbl t2
t1.IDnum = t2.IDnum
---------------------- --------------------------------
在Access Jet Sql中运行。 Access Jet可以使用很多tSql语句
Sql（它都是微软）。但是Jet sql是专为桌面使用而设计的，而不是企业用途，因此期望
限制是不合理的。否则，为什么要有桌面和企业系统？为什么
不只是一个系统？因为不是每个人都需要一个企业系统。
我不知道Jet Sql能做多少才能做到这一点。我只是询问是否有人知道如何执行如上所述的tSql更新
一次性访问请分享。我的感觉是Jet Sql不是用于执行这种操作的
。但我不确定。
>>
我很惊讶有多少人似乎认为如果某些东西在他们最喜欢的软件中工作，它应该有效在其他软件中没有改变
<<
---------------------- -------------------------
Dim DB作为DAO数据库，RS作为DAO.RecordSet
Dim strsql As String，strPhone As String，intID As Integer
设置DB = CurrentDB
设置RS = DB.OpenRecordset（" ImportTable"）
请不要RS.EOF
strPhone = RS！电话
intID = RS！IDfield
strSql = 更新mainTbl Set Phone =''" &安培; strPhone _
& "'''IDnum =" &安培; intID
DoCmd.RunSql strSql
RS.MoveNext
循环
--------- ------------------------------------------
这是tSql语句的it-works-In-Access改进版本
以上。很明显，tSql语句更简单，更有效。
有效。
是的，可以让Access执行.Net或C ++可以做的任何事情
如果你想用大量的API代码浪费你的时间（这是用C ++编写的所有用b
，就像Access和.Net一样）但是这就像
用手铲挖一个1000立方英尺的沟而不是蒸汽
铲（sql Server）。
Rich
***通过开发人员指南 http：// www。 developersdex.com ***
I have a contacts table with name address and such but are missing all phone
numbers in the phone number fields. I have just received an updated
customer list in Excel and have imported into a new table.
Can I run a query that will take the phone number only from the new table
and update the old tables phone number fields from empty to the proper
information?
Hi John,
In sql Server you can run a Transact sql statement like this:
strSql = "Update t1 Set t1.Phone = t2.Phone from mainTbl t1 Join
ImportTbl t2 on t1.IDnum = t2.IDnum"
to update a table in one shot.
But I was not able to get it to work in Access - it must be a limitation
of Access Jet Sql. If anyone knows how to make this kind of sql
statement work in Access - please share.
The alternative for Access that will work is to use DAO with sql and
loop through your import Table:
-----------------------------------------------
Dim DB As DAO Database, RS As DAO.RecordSet
Dim strsql As String, strPhone As String , intID As Integer
Set DB = CurrentDB
Set RS = DB.OpenRecordset("ImportTable")
Do While Not RS.EOF
strPhone = RS!Phone
intID = RS!IDfield
strSql = "Update mainTbl Set Phone = ''" & strPhone _
& "'' Where IDnum = " & intID
DoCmd.RunSql strSql
RS.MoveNext
Loop
---------------------------------------------------
This method only updates one row at a time, but it works.
Rich
*** Sent via Developersdex http://www.developersdex.com ***
"Rich P" wrote
In sql Server you can run a Transact
sql statement like this:
strSql = "Update t1 Set t1.Phone = t2.Phone from mainTbl t1 Join
ImportTbl t2 on t1.IDnum = t2.IDnum"
to update a table in one shot.
But I was not able to get it to work in Access -
it must be a limitation of Access Jet Sql. If
anyone knows how to make this kind of sql
statement work in Access - please share.I am amazed at how many people seem to think that if something works in
their favorite software, it should work unaltered in other software --
especially people intelligent enough to create working SQL in a server DB.
In Access, you use the Query Builder: add each of the Tables as a data
source, join them on the common unique identification fields, drag down the
necessary fields, click on Query in the menu, choose Update, then set the
UpdateTo of the telephone number in the original table to be updated to the
telephone number in the new table.
If you really feel compelled to look at the Jet SQL, you can click the
leftmost icon on the toolbar or choose View | SQL from the design view menu
in Query Builder.
Larry Linson
Microsoft Access MVP
>>
I am amazed at how many people seem to think that if something works in
their favorite software, it should work unaltered in other software --
especially people intelligent enough to create working SQL in a server
DB.
...
Larry Linson
Microsoft Access MVP
<<
Neither you nor anyone else has demonstrated how to make a Tsql
statement like this one
-----------------------------------------------------
Update t1 Set t1.Phone = t2.Phone from mainTbl t1 Join ImportTbl t2 on
t1.IDnum = t2.IDnum
------------------------------------------------------
run in Access Jet Sql. A lot tSql statements can be used by Access Jet
Sql (it is all microsoft). But Jet sql is designed for desktop useage
not Enterprise usage, therefore it is not unreasonable to expect
limitations. Otherwise, why have desktop and enterprise systsms? Why
not just one system? Because not everyone needs an enterprise system.
I don''t happen to know how much Jet Sql can do that tSql can do. I am
merely asking if anyone knows how to perform a tSql update as above in
one shot in Access to please share. My feeling is that Jet Sql is not
designed to perform such an operation. But I don''t know for sure.
>>
I am amazed at how many people seem to think that if something works in
their favorite software, it should work unaltered in other software
<<
-----------------------------------------------
Dim DB As DAO Database, RS As DAO.RecordSet
Dim strsql As String, strPhone As String , intID As Integer
Set DB = CurrentDB
Set RS = DB.OpenRecordset("ImportTable")
Do While Not RS.EOF
strPhone = RS!Phone
intID = RS!IDfield
strSql = "Update mainTbl Set Phone = ''" & strPhone _
& "'' Where IDnum = " & intID
DoCmd.RunSql strSql
RS.MoveNext
Loop
---------------------------------------------------
This is the it-works-In-Access altered version of the tSql statement
above. Clearly, the tSql statement is much simpler and much more
efficient.
Yes, it is possible to make Access do anything that .Net or C++ can do
if you want to waste your time with tons of API code (which is all
written in C++ - just like Access is and .Net is) but that would be like
using a hand shovel to dig a 1000 cubic foot ditch instead of a steam
shovel (sql Server).
Rich
*** Sent via Developersdex http://www.developersdex.com ***
这篇关于使用另一个表中的特定信息更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
