I have a select query which does some text manipulation to essentially reformat a field so that I can look it up in another table:
我有一个select查询，它做了一些文本操作来重新格式化一个字段以便我可以在另一个表中查找:
If my first table if I have a field like "J1/2" it looks up the ID of a record in a different table with J1 and J2 in the appropriate fields.
如果我的第一个表，如果我有一个像“J1/2”这样的字段，它会在另一个表中查找记录的ID，在适当的字段中使用J1和J2。
This all works well.
这一切顺利。
Now I want to update the original table so I don't have to do lookups using this string manipulation anymore, but my attempts at update queries end with "Operation must use an updateable query"
现在我想要更新原始表，这样我就不用再使用这个字符串操作进行查找了，但是我的更新查询尝试以“操作必须使用可更新查询”结尾
Any ideas?
什么好主意吗?
My SELECT statement:
我的SELECT语句:
SELECT DISTINCT
t1.DD,
t1.TN,
t1.DD & " J" & MID(t1.TN,2,1) AS CalculatedStart,
t1.DD & " J" & MID(t1.TN,4,1) AS CalculatedEnd,
t2.ID
FROM t1 INNER JOIN t2
ON (t1.DD & " J" & MID(t1.TN,2,1)=t2.StartLink)
AND (t1.DD & " J" & MID(t1.TN,4,1)=t2.EndLink)
WHERE t1.TN Like "J?/?"
AND t1.DD Like "M*";
Recall - this works fine and I get the necessary t2.ID out the other end.
回想一下，这很好，我得到了必要的t2。找出另一端。
So I want to do something like:
所以我想做的是:
UPDATE t1 SET t2ID = (
SELECT Query1.ID
FROM Query1
WHERE t1.DD=Query1.DD
AND t1.TN=Query1.TN
)
WHERE t1.TN Like "J?/?"
AND t1.DD Like "M*";
Only this fails. This is within MS Access itself so I can't imagine an actual permissions problem like most of the "Operation must use an updateable query" problems seem to be.
只有这个失败。这是在MS访问本身中，所以我无法想象实际的权限问题，就像大多数“操作必须使用可更新查询”的问题一样。
EDIT: Trying to simplify the case that doesn't work.
编辑:试图简化不能工作的情况。
This UPDATE query is fine:
此更新查询没有问题:
UPDATE t1
SET t2ID="Unknown"
WHERE TN LIKE "J?/?"
AND DD LIKE "M*";
This one fails (Thanks Goedke - this example obviously fails because the subquery returns more than 1 result. I had oversimplified to try to find my problem)
这个失败(感谢Goedke)——这个例子显然失败了，因为子查询返回的结果不止一个。我已经过度简化了，试图找到我的问题)
UPDATE t1
SET t2ID=(SELECT ID FROM t2)
WHERE TN LIKE "J?/?"
AND DD LIKE "M*";
So do I just have my subquery syntax wrong in some way?
我的子查询语法是否有问题?
EDIT: This SELECT statement is fine too:
编辑:这个选择语句也可以:
SELECT t1.OA, t1.DD, t1.TN, t1.HATRIS,
query1.DD, query1.TN, query1.ID
FROM t1 INNER JOIN query1
ON t1.DD=query1.DD
AND t1.TN=query1.TN
Furthermore, using count on the select statement above shows that there is exactly 1 ID being returned per (DD,TN) combination
此外，在上面的select语句中使用count显示，每个(DD,TN)组合中返回的ID恰好是1。
EDIT:
编辑:
The simplest case I've now got to - using various SELECT statements I now have a table with just 2 columns - the primary key of t1 and the value I want to insert into t1.
最简单的例子是——使用各种SELECT语句，现在有一个只有2列的表——t1的主键和我想要插入t1的值。
I still can't seem to write
我还是不能写。
UPDATE t1 SET t1.f2 = (SELECT t2.f2 FROM t2 WHERE t2.f1 = t1.f1)
where t1's primary key is f1. Even adding WHERE t1.f1 IN (SELECT f1 FROM t2) doesn't help. (Added to eliminate the possibility that the subquery returns 0 results)
其中t1的主键是f1。即使添加t1的地方。f1 IN(从t2中选择f1)没有帮助。(增加了消除子查询返回0结果的可能性)
7 个解决方案
#1
6
A subquery of (SELECT ID FROM t2) can't work unless there is only one record in t2. Which ID are you expecting to be used?
除非t2中只有一条记录，否则(从t2中选择ID)的子查询不能工作。您希望使用哪个ID ?
The error message that is being reported normally occurs when you have joins and are not including all of the primary keys necessary to update back to tables in a data bound form (for example, your original DISTINCT destroys information about keys, so if it was bound to a form, the form would not be able to save back).
被报告的错误消息通常发生在连接和不包括所有必要的主键更新回表数据绑定的形式(例如,原来不同的破坏键信息,所以如果是绑定到表单,表单将无法保存)。
The fact you are using DISTINCT there would make me suspicious that the sub query is returning more than one row in your more complex example. This is probably the most common problem with assigning out of a sub query result: under-constraining the where clause.
您在那里使用了DISTINCT，这使我怀疑子查询在更复杂的示例中返回了不止一行。这可能是分配子查询结果的最常见问题:对where子句的限制不足。
Another problem I have seen with assigning out of a subquery is if the syntax of the inner query is incorrect. At least with SQL 2000 and 2005 back ends, the query processor will silently fail and return NULL in such cases. (This is, as far as I can tell, a bug: I see no reason why something that will return an error at the top level would be silently permitted in a subquery... but there it is.)
我从子查询中看到的另一个问题是，内部查询的语法是否正确。至少在SQL 2000和2005后端，查询处理器将静默失败，并在这种情况下返回NULL。(就我所知，这是一个bug:我看不出为什么会在顶层返回错误的东西会在子查询中被静默地允许……)但在这里。)
EDIT: Just to ensure that neither Paul or I wasn't going crazy, I created the following tables:
编辑:为了确保保罗和我都不会疯，我创建了以下表格:
t1 | ID, FK, Data
t2 | ID2, Data2
I did not put any constraints except a primary key on ID and ID2. All fields were text, which is different from what I normally use for IDs, but should be irrelevant.
除了ID和ID2上的主键外，我没有设置任何约束。所有字段都是文本，这与我通常使用的id不同，但应该是不相关的。
t1:
t1:
ID FK Data
Key1 Data1
Key2 Data2
Key3 Data3
t2:
t2:
ID2 Data2
Key1 DataA
Key2 DataB
Key3 DataC
A query of the form:
查询表格:
UPDATE t1 SET t1.FK = (select ID2 from t2 where t2.ID2 = t1.ID);
Failed with the same message Paul got.
保罗得到了同样的信息。
select *, (select ID2 from t2 where t2.ID2 = t1.ID) as foreign from t1,
works as expected, so we know the subquery syntax is not to blame.
工作正常，所以我们知道子查询语法不应该受到责备。
UPDATE t1 SET t1.FK = 'Key1'
also works as expected, so we don't have a corrupt or non updateable destination.
也可以按预期工作，因此我们没有损坏或不可更新的目的地。
Note: if I change the database backend from native to SQL 2005, the update works! A bit of googling around, and I find Access MVPs suggesting DLOOKUP to replace a subquery:
注意:如果我将数据库后端从本机更改为SQL 2005，那么更新就会正常工作!我搜索了一下，发现Access MVPs建议用DLOOKUP代替子查询:
http://www.eggheadcafe.com/software/aspnet/31849054/update-with-subquerycomp.aspx
http://www.eggheadcafe.com/software/aspnet/31849054/update-with-subquerycomp.aspx
Apparently this is a bug in Access SQL, one that is avoided when using a SQL Express 2000 or higher back end. (The google results for "access update subquery" support this theory).
显然，这是访问SQL中的一个错误，在使用SQL Express 2000或更高的后端时可以避免这种错误。(“访问更新子查询”的谷歌结果支持这一理论)。
See here for how to use this workaround: http://www.techonthenet.com/access/functions/domain/dlookup.php
请看这里，了解如何使用这个变通方法:http://www.techonthenet.com/access/functions/domain/dlookup.php
#2
17
I have to weigh in with David W. Fenton's comment on the OP.
我得和大卫·芬顿(David W. Fenton)谈谈对这次行动的看法。
This is highly annoying problem with Jet/ACE. But try either:
这是一个非常烦人的问题。但尝试:
- go to the query properties (click the background of the pane where the tables are displayed) and set 'Unique Records' to 'Yes'
- 转到查询属性(单击显示表的窗格的背景)并将“惟一记录”设置为“Yes”
- Option 1 is the equivalent of adding the somewhat strange looking
DISTINCTROWkeyword to theSELECTclause, eg - 选项1等价于向SELECT子句(例如)添加看起来有点奇怪的DISTINCTROW关键字
:
:
UPDATE DISTINCTROW tblClient
INNER JOIN qryICMSClientCMFinite
ON tblClient.ClientID = qryICMSClientCMFinite.ClientID
SET tblClient.ClientCMType = "F";
This solves so many problems involving this error message that it is almost ridiculous.
这解决了许多与错误消息相关的问题，几乎是荒谬的。
That's MS Access in a nutshell - if you don't know the trade-secret workaround for problem x, you can take days trying to find the answer. To know the 10,000 workarounds IS to program Access. Is that enough of a warning for the uninitiated ?
简而言之，这就是MS Access——如果你不知道问题x的商业秘密，你可以花上几天的时间去寻找答案。要了解这10,000个工作区，就需要编程访问。这对不知情的人来说算是一个警告了吗?
Ben
本
#3
7
This worked for me (Access 2000)
这对我有用(Access 2000)
UPDATE DISTINCTROW T1 inner join T2 on T2.f1 = T1.f1 SET f2 = f2;
#4
6
I havent't read the whole thread, but this is the solution that I am using:
我没有读过整个线程，但这是我使用的解决方案:
update (select * from t1 inner join t2 on t1.key = t2.key) set t1.field1 = t2.field2
and that works fine in MS Access for me.
对我来说，在MS Access中运行得很好。
#5
1
My solution was to change my sql on that way.
我的解决方案是用这种方式更改sql。
update (select o.pricein, g.pricein from operations o left join goods g on g.id = o.goodid where o.opertype = 4 and o.acct = 1)
set o.pricein = g.pricein
#6
1
I had the same error ("Operation must use an updateable query") using Access 2010 and I was performing a simple update query with an inner join. All I did was add a primary key to the table I was joining on (already had one, of course, on the table I was updating) and everything worked.
我有相同的错误(“操作必须使用一个可更新的查询”)使用Access 2010，而我正在执行一个带有内部连接的简单更新查询。我所做的就是为我正在加入的表添加一个主键(当然，我正在更新的表上已经有了一个主键)，一切都运行良好。
#7
0
For this one: UPDATE t1 SET t1.f2 = (SELECT t2.f2 FROM t2 WHERE t2.f1 = t1.f1)
对于这个:更新t1集t1。f2 =(选择t2。从t2得到f2。f1 = t1.f1)
UPDATE t1 INNER JOIN t2 ON t1.f1 = t2.f1 SET t1.f2 = [t2].[f2];
#1
6
A subquery of (SELECT ID FROM t2) can't work unless there is only one record in t2. Which ID are you expecting to be used?
除非t2中只有一条记录，否则(从t2中选择ID)的子查询不能工作。您希望使用哪个ID ?
The error message that is being reported normally occurs when you have joins and are not including all of the primary keys necessary to update back to tables in a data bound form (for example, your original DISTINCT destroys information about keys, so if it was bound to a form, the form would not be able to save back).
被报告的错误消息通常发生在连接和不包括所有必要的主键更新回表数据绑定的形式(例如,原来不同的破坏键信息,所以如果是绑定到表单,表单将无法保存)。
The fact you are using DISTINCT there would make me suspicious that the sub query is returning more than one row in your more complex example. This is probably the most common problem with assigning out of a sub query result: under-constraining the where clause.
您在那里使用了DISTINCT，这使我怀疑子查询在更复杂的示例中返回了不止一行。这可能是分配子查询结果的最常见问题:对where子句的限制不足。
Another problem I have seen with assigning out of a subquery is if the syntax of the inner query is incorrect. At least with SQL 2000 and 2005 back ends, the query processor will silently fail and return NULL in such cases. (This is, as far as I can tell, a bug: I see no reason why something that will return an error at the top level would be silently permitted in a subquery... but there it is.)
我从子查询中看到的另一个问题是，内部查询的语法是否正确。至少在SQL 2000和2005后端，查询处理器将静默失败，并在这种情况下返回NULL。(就我所知，这是一个bug:我看不出为什么会在顶层返回错误的东西会在子查询中被静默地允许……)但在这里。)
EDIT: Just to ensure that neither Paul or I wasn't going crazy, I created the following tables:
编辑:为了确保保罗和我都不会疯，我创建了以下表格:
t1 | ID, FK, Data
t2 | ID2, Data2
I did not put any constraints except a primary key on ID and ID2. All fields were text, which is different from what I normally use for IDs, but should be irrelevant.
除了ID和ID2上的主键外，我没有设置任何约束。所有字段都是文本，这与我通常使用的id不同，但应该是不相关的。
t1:
t1:
ID FK Data
Key1 Data1
Key2 Data2
Key3 Data3
t2:
t2:
ID2 Data2
Key1 DataA
Key2 DataB
Key3 DataC
A query of the form:
查询表格:
UPDATE t1 SET t1.FK = (select ID2 from t2 where t2.ID2 = t1.ID);
Failed with the same message Paul got.
保罗得到了同样的信息。
select *, (select ID2 from t2 where t2.ID2 = t1.ID) as foreign from t1,
works as expected, so we know the subquery syntax is not to blame.
工作正常，所以我们知道子查询语法不应该受到责备。
UPDATE t1 SET t1.FK = 'Key1'
also works as expected, so we don't have a corrupt or non updateable destination.
也可以按预期工作，因此我们没有损坏或不可更新的目的地。
Note: if I change the database backend from native to SQL 2005, the update works! A bit of googling around, and I find Access MVPs suggesting DLOOKUP to replace a subquery:
注意:如果我将数据库后端从本机更改为SQL 2005，那么更新就会正常工作!我搜索了一下，发现Access MVPs建议用DLOOKUP代替子查询:
http://www.eggheadcafe.com/software/aspnet/31849054/update-with-subquerycomp.aspx
http://www.eggheadcafe.com/software/aspnet/31849054/update-with-subquerycomp.aspx
Apparently this is a bug in Access SQL, one that is avoided when using a SQL Express 2000 or higher back end. (The google results for "access update subquery" support this theory).
显然，这是访问SQL中的一个错误，在使用SQL Express 2000或更高的后端时可以避免这种错误。(“访问更新子查询”的谷歌结果支持这一理论)。
See here for how to use this workaround: http://www.techonthenet.com/access/functions/domain/dlookup.php
请看这里，了解如何使用这个变通方法:http://www.techonthenet.com/access/functions/domain/dlookup.php
#2
17
I have to weigh in with David W. Fenton's comment on the OP.
我得和大卫·芬顿(David W. Fenton)谈谈对这次行动的看法。
This is highly annoying problem with Jet/ACE. But try either:
这是一个非常烦人的问题。但尝试:
- go to the query properties (click the background of the pane where the tables are displayed) and set 'Unique Records' to 'Yes'
- 转到查询属性(单击显示表的窗格的背景)并将“惟一记录”设置为“Yes”
- Option 1 is the equivalent of adding the somewhat strange looking
DISTINCTROWkeyword to theSELECTclause, eg - 选项1等价于向SELECT子句(例如)添加看起来有点奇怪的DISTINCTROW关键字
:
:
UPDATE DISTINCTROW tblClient
INNER JOIN qryICMSClientCMFinite
ON tblClient.ClientID = qryICMSClientCMFinite.ClientID
SET tblClient.ClientCMType = "F";
This solves so many problems involving this error message that it is almost ridiculous.
这解决了许多与错误消息相关的问题，几乎是荒谬的。
That's MS Access in a nutshell - if you don't know the trade-secret workaround for problem x, you can take days trying to find the answer. To know the 10,000 workarounds IS to program Access. Is that enough of a warning for the uninitiated ?
简而言之，这就是MS Access——如果你不知道问题x的商业秘密，你可以花上几天的时间去寻找答案。要了解这10,000个工作区，就需要编程访问。这对不知情的人来说算是一个警告了吗?
Ben
本
#3
7
This worked for me (Access 2000)
这对我有用(Access 2000)
UPDATE DISTINCTROW T1 inner join T2 on T2.f1 = T1.f1 SET f2 = f2;
#4
6
I havent't read the whole thread, but this is the solution that I am using:
我没有读过整个线程，但这是我使用的解决方案:
update (select * from t1 inner join t2 on t1.key = t2.key) set t1.field1 = t2.field2
and that works fine in MS Access for me.
对我来说，在MS Access中运行得很好。
#5
1
My solution was to change my sql on that way.
我的解决方案是用这种方式更改sql。
update (select o.pricein, g.pricein from operations o left join goods g on g.id = o.goodid where o.opertype = 4 and o.acct = 1)
set o.pricein = g.pricein
#6
1
I had the same error ("Operation must use an updateable query") using Access 2010 and I was performing a simple update query with an inner join. All I did was add a primary key to the table I was joining on (already had one, of course, on the table I was updating) and everything worked.
我有相同的错误(“操作必须使用一个可更新的查询”)使用Access 2010，而我正在执行一个带有内部连接的简单更新查询。我所做的就是为我正在加入的表添加一个主键(当然，我正在更新的表上已经有了一个主键)，一切都运行良好。
#7
0
For this one: UPDATE t1 SET t1.f2 = (SELECT t2.f2 FROM t2 WHERE t2.f1 = t1.f1)
对于这个:更新t1集t1。f2 =(选择t2。从t2得到f2。f1 = t1.f1)
UPDATE t1 INNER JOIN t2 ON t1.f1 = t2.f1 SET t1.f2 = [t2].[f2];