MySQL 更新查询失败，错误：1175
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18767308/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update query failing with error : 1175
提问by Firu
I am trying to update a table using the following query
我正在尝试使用以下查询更新表
update at_product A join
(
SELECT atbillfields.billeditemguid,count(*) AS numberOfPeopleBought
,sum(atbillfields.billeditemqty) AS soldquantity
FROM jtbillingtest.at_salesbill atsalesbill
JOIN jtbillingtest.at_billfields atbillfields
ON atsalesbill.billbatchguid=atbillfields.billbatchguid
WHERE atsalesbill.billcreationdate BETWEEN '2013-09-09' AND date_add('2013-09-09', INTERVAL 1 DAY)
GROUP BY atbillfields.billeditemguid) B ON B.billeditemguid = A.productguid
SET A.productQuantity = A.productQuantity - B.soldquantity
But, getting the following exception:
但是，得到以下异常：
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.
错误代码：1175。您正在使用安全更新模式，并且您尝试更新没有使用 KEY 列的 WHERE 的表要禁用安全模式，请切换首选项 -> SQL 查询中的选项并重新连接。
When I gave a whereclause with the update like A.productQuantity = 1, it updated that particular.
当我给出一个where带有更新的子句时A.productQuantity = 1，它更新了那个特定的内容。
Can someone point why I am unable to execute the query and how to solve the issue?
有人可以指出为什么我无法执行查询以及如何解决问题吗？
回答by Menios
Have a look at:
看一下：
If you want to update without a where key you must execute
如果您想在没有 where 键的情况下进行更新，则必须执行
SET SQL_SAFE_UPDATES=0;
right before your query.
就在您的查询之前。
Another option is to rewrite your query o include a key.
另一种选择是重写您的查询 o 包括一个键。
回答by Joe Minichino
This error means you're operating in safe update mode and therefore you have two options:
此错误意味着您正在安全更新模式下运行，因此您有两个选择：
- you need to provide a where clause that includes an index for the update to be successful or
- You can disable this feature by doing
SET SQL_SAFE_UPDATES = 0;
- 您需要提供一个 where 子句，其中包含更新成功的索引或
- 您可以通过执行以下操作禁用此功能
SET SQL_SAFE_UPDATES = 0;
回答by Le Anh
You can try on MysqlWorkbench
你可以试试MysqlWorkbench
Go to Edit --> Preferences
转到编辑 --> 首选项
Click "SQL Editor" tab and uncheck "Safe Updates" check box
单击“SQL 编辑器”选项卡并取消选中“安全更新”复选框
Query --> Reconnect to Server (logout and then login)
查询 --> 重新连接到服务器（注销然后登录）
I hope it is helpful for you.
我希望它对你有帮助。
回答by Le Anh
In MySQL 5.5, if you're using MySQL Workbenchthen
在MySQL 5.5，如果你正在使用MySQL Workbench那么
- Go to
Edit-->Preferences - Click
"SQL Queries"tab and uncheck"Safe Updates"check box. Query-->Reconnect to Server (logoutand thenlogin)
- 去
Edit-->Preferences - 单击
"SQL Queries"选项卡并取消"Safe Updates"选中复选框。 Query-->重新连接到服务器（logout然后login）
This works.
这有效。
