更新OPENQUERY失败
我在尝试对 SQL Server 中的 MySql 数据库执行以下 OPENQUERY 语句时遇到错误。
UPDATE OPENQUERY(MYWPDB, 'SELECT total FROM wp_tt WHERE id = 112121') SET total = 1
错误是“关键列信息不足或不正确。更新影响了太多行”。
该语句应将“total”字段的值更新为“1”。它是一个整数字段，“id”是表上的主键。我正在使用 SQL Server 2000。
I'm getting an error in the following OPENQUERY statement that I'm trying to execute against a MySql database from SQL Server.
UPDATE OPENQUERY(MYWPDB, 'SELECT total FROM wp_tt WHERE id = 112121') SET total = 1
The error is "Key column information is insufficient or incorrect. Too many rows were affected by update".
The statement should be updating the 'total' field to the value of '1'. It's an integer field and 'id' is the primary key on the table. I'm using SQL Server 2000.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我在更新 iSeries 的 openquery 时遇到了同样的问题。我的 openquery 也在游标内。
修复方法是在选择中包含关键列。
所以在你的情况下它会是这样的：
I had the same issue with an openquery that updates iSeries. My openquery is within a cursor also.
The fix is to include the key columns in the select.
So in your case it would be something like this:
事实证明查询没有任何问题。我试图在存储过程中的游标操作中执行该语句。我在游标操作之外测试了它，并且处理得很好。
然而，由于我仍然需要它在光标内工作，所以我必须继续挖掘，最后发现四部分语法可以解决问题。所以查询变成了：
Turns out there's nothing wrong with the query. I was trying to execute the statement inside a cursor operation inside a stored procedure. I tested it outside the cursor operation and it processed fine.
However, since I still needed it to work within the cursor, I had to keep digging, and finally found the four-part syntax would do the trick. So the query instead became: