更新查询内部加入问题 [英] Update query Inner Join question
问题描述
我已经尝试了以下各项，但没有运气。
更新tblEntity为tbl INNER JOIN search3220 as qry ON tbl.Entity_ID =
qry.Entity_ID SET tbl.Cat_ID = 289;
UPDATE tblEntity INNER JOIN search3220 ON tblEntity.Entity_ID =
seach3220.Entity_ID SET tblEntity.Cat_ID = 289 ;
除了语法之外，我能想象的唯一问题是Entity_ID在查询中不是唯一的
唯一。对于
示例，tblEntity只有一个Entity_ID为1700，而search3220可能有几个Entity_ID为1700.这是
问题吗？或者是语法问题？
提前谢谢。
I''ve tried each of the below, but no luck.
UPDATE tblEntity As tbl INNER JOIN search3220 As qry ON tbl.Entity_ID =
qry.Entity_ID SET tbl.Cat_ID = 289;
UPDATE tblEntity INNER JOIN search3220 ON tblEntity.Entity_ID =
seach3220.Entity_ID SET tblEntity.Cat_ID = 289;
The only issue I can imagine aside from syntax is that Entity_ID is not
unique in the query. tblEntity will only have one Entity_ID of 1700, for
example, while search3220 may have several Entity_IDs of 1700. Is this the
problem? Or is it a syntax issue?
Thanks in advance.
推荐答案
我弄清楚了：<登记/>
UPDATE DISTINCTROW tblEntity INNER JOIN search3220 ON tblEntity.Entity_ID =
search3220.Entity_ID SET tblEntity.Cat_ID = QryPrm（QUOT; frmTools"，" frmCatAdm" ;,
" cbxNewCat"）;
Jet出于某种原因需要DISTINCTROW声明...
现在唯一的问题是查询search3220是在
上动态创建的 - 它可能是search2110或者其他什么 - 我不知道它是什么，直到
运行时。我可以使用代码构建一个字符串，并使用类似
" db.Execute strSql" - 但我需要编译查询的性能。
无论如何都要搜索qureyDefs集合并将查询名称
插入到编译查询中，类似于我正在使用的QryPrm
函数？
I figured it out:
UPDATE DISTINCTROW tblEntity INNER JOIN search3220 ON tblEntity.Entity_ID =
search3220.Entity_ID SET tblEntity.Cat_ID = QryPrm("frmTools", "frmCatAdm",
"cbxNewCat");
Jet wants the DISTINCTROW statement for some reason...
The only issue now is that query search3220 is created dynamically on the
fly - it could be search2110 or whatever - I don''t know what it is until
runtime. I could build a string with code and use something like
"db.Execute strSql" - but I need the performance of a compiled query.
Is there anyway to search the qureyDefs collection and insert the query name
into a compiled query, something similar to what I''m doing with the QryPrm
function?
" deko" < www.clearpointsystems.com@use_contact_form.com>在消息中写道
新闻：O0 ***************** @ newssvr14.news.prodigy.co m ...
"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:O0*****************@newssvr14.news.prodigy.co m...
我计算出来：
UPDATE DISTINCTROW tblEntity INNER JOIN search3220 ON tblEntity.Entity_ID
= search3220.Entity_ID SET tblEntity.Cat_ID = QryPrm（QUOT; frmTools" ;,
" frmCatAdm"，" cbxNewCat"）;
Jet出于某种原因需要DISTINCTROW语句...
现在唯一的问题是查询search3220是动态创建的<飞 - 它可能是search2110或其他 - 我不知道它是什么，直到
运行时。我可以用代码构建一个字符串，并使用类似
db.Execute strSql的东西。 - 但我需要编译查询的性能。
无论如何都要搜索qureyDefs集合并将查询
名称插入到编译查询中，类似于我正在做的事情使用QryPrm
函数？
I figured it out:
UPDATE DISTINCTROW tblEntity INNER JOIN search3220 ON tblEntity.Entity_ID = search3220.Entity_ID SET tblEntity.Cat_ID = QryPrm("frmTools", "frmCatAdm", "cbxNewCat");
Jet wants the DISTINCTROW statement for some reason...
The only issue now is that query search3220 is created dynamically on the
fly - it could be search2110 or whatever - I don''t know what it is until
runtime. I could build a string with code and use something like
"db.Execute strSql" - but I need the performance of a compiled query.
Is there anyway to search the qureyDefs collection and insert the query name into a compiled query, something similar to what I''m doing with the QryPrm
function?
以下是如何遍历QueryDef
集合的准系统示例。
这将在VBA IDE立即窗口中显示所有查询名称和SQL。
Public Sub ShowQueryDefs（）
Dim db作为DAO.Database
Dim qds作为DAO.QueryDefs
Dim qd作为DAO.QueryDef
设置db = CurrentDb（）
设置qds = db.QueryDefs
每个qd in qds
Debug.Print qd.Name
Debug.Print qd.SQL
下一个qd
设置qd = Nothing
db.Close
设置db = Nothing
End Sub
要做你需要做的事，修改上面的内容来检查每个SQL字符串对于
w hatever它正在寻找，然后动态组装一个新的SQL
字符串，包含你需要的任何内容，然后使用qd.sql =
SQLString ;。
Here is a barebones example of how to iterate through the QueryDef
collection.
This displays all Query names and SQL in the VBA IDE Immediate Window.
Public Sub ShowQueryDefs()
Dim db As DAO.Database
Dim qds As DAO.QueryDefs
Dim qd As DAO.QueryDef
Set db = CurrentDb()
Set qds = db.QueryDefs
For Each qd In qds
Debug.Print qd.Name
Debug.Print qd.SQL
Next qd
Set qd = Nothing
db.Close
Set db = Nothing
End Sub
To do what you need to do, modify the above to check each SQL string for
whatever it is you''re looking for, then dynamically assemble a new SQL
string containing whatever it is you need, and then use "qd.sql =
SQLString".
>下面是如何遍历QueryDef
> Here is a barebones example of how to iterate through the QueryDef
集合的简单示例。
这将在VBA IDE立即窗口中显示所有查询名称和SQL。
Public Sub ShowQueryDefs（）
Dim db作为DAO.Database
Dim qds作为DAO.QueryDefs
Dim qd作为DAO.QueryDef
设置db = CurrentDb（）
设置qds = db.QueryDefs
每个qd在qds中
Debug.Print qd.Name
Debug.Print qd.SQL
下一个qd
设置qd = Nothing
db.Close
设置db = Nothing
End Sub
To做你需要做的事情，修改上面的内容来检查每个SQL字符串是否为你正在寻找的东西，然后动态组装一个新的SQL
字符串，包含你需要的东西，然后使用qd.sql =
SQLString。
collection.
This displays all Query names and SQL in the VBA IDE Immediate Window.
Public Sub ShowQueryDefs()
Dim db As DAO.Database
Dim qds As DAO.QueryDefs
Dim qd As DAO.QueryDef
Set db = CurrentDb()
Set qds = db.QueryDefs
For Each qd In qds
Debug.Print qd.Name
Debug.Print qd.SQL
Next qd
Set qd = Nothing
db.Close
Set db = Nothing
End Sub
To do what you need to do, modify the above to check each SQL string for
whatever it is you''re looking for, then dynamically assemble a new SQL
string containing whatever it is you need, and then use "qd.sql =
SQLString".
所以，基本上，我只是使用SQL字符串创建一个QueryDef对象 - whic h $>
包含动态创建的查询的名称。这听起来很合理。
但是我想知道这样做的开销是否值得编译查询提供的性能提升。
两者之间是否有很大的性能差异：
db.Execute strSomeSqlString
和
db.Execute" qryQueryName"
？
感谢您的帮助！
So, basically, I just create a QueryDef object using an SQL string - which
contains the name of the dynamically-created query. This sounds reasonable.
But I''m wondering if the overhead of doing this is worth whatever
performance gains a compiled query will provide.
Is there much of a performance difference between:
db.Execute strSomeSqlString
and
db.Execute "qryQueryName"
?
Thanks for the help!
这篇关于更新查询内部加入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
