如何在mySQL中同时选择和更新一条记录？
有没有什么方法可以选择一条记录并在单个查询中更新它？
我尝试了这个：
UPDATE arrc_Voucher
SET ActivatedDT = now()
WHERE (SELECT VoucherNbr, VoucherID
FROM arrc_Voucher
WHERE ActivatedDT IS NULL
AND BalanceInit IS NULL
AND TypeFlag = 'V'
LIMIT 1 )
我希望运行选择查询并获取与 where 子句匹配的第一条记录，更新该记录中的 ActivatedDT 字段，但出现以下错误：
1241 - 操作数应包含 1 列
有什么想法吗？
Is there any way to select a record and update it in a single query?
I tried this:
UPDATE arrc_Voucher
SET ActivatedDT = now()
WHERE (SELECT VoucherNbr, VoucherID
FROM arrc_Voucher
WHERE ActivatedDT IS NULL
AND BalanceInit IS NULL
AND TypeFlag = 'V'
LIMIT 1 )
which I hoped would run the select query and grab the first record that matches the where clause, the update the ActivatedDT field in that record, but I got the following error:
1241 - Operand should contain 1 column(s)
Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
怎么样：
How about:
来自 MySQL API 文档：
您不能选择一行并同时更新它，您将需要执行两个查询才能实现它；获取您的记录，然后更新它。
如果您担心并发进程通过某种竞争条件访问同一行（假设您的用例涉及高流量），您可以考虑其他替代方案，例如 锁定表（请注意，如果表在访问时被锁定，其他进程将需要恢复--重试）
或者如果您可以创建存储过程，您可能需要阅读 本文或MySQL API 文档。
但大约 99% 的情况下，这是没有必要的，这两个查询将毫无问题地执行。
From the MySQL API documentation :
You cannot select a row and update it at the same time, you will need to perform two queries to achieve it; fetch your record, then update it.
If you are worrying about concurrent processes accessing the same row through some kind of race condition (supposing your use case involve high traffic), you may consider other alternatives such as locking the table (note that other processes will need to recover--retry--if the table is locked while accessing it)
Or if you can create stored procedure, you may want to read this article or the MySQL API documentation.
But about 99% of the time, this is not necessary and the two queries will execute without any problem.