是否可以将一个表中的值插入到另一个表中并更新同一查询中的字段值？
我有两张桌子。一张表用作交易历史记录，另一张表用于记录会员详细信息。运行报告时，我想将部分成员详细信息移至交易历史记录中，但还更新一些原本不存在的字段记录。
是否可以选择满足特定条件的所有记录，仅将匹配行的片段插入到另一个表中并在单个查询中更新其他字段？
例如：
在表 2 中，我有会员姓名、注册日期和会员 ID。我想将上述记录移至表 1 中，同时将字段（状态）更新为“已处理”。
注意：我还使用 php 和 pdo 连接到 mysql 数据库。
这可以在单个查询中实现吗？
I have two tables. One table is meant to serve as a transaction history and the other is a log of member details. When a report is run, I want to move pieces of the member details into the transaction history but ALSO update some field records which would not otherwise exist.
Is it possible to select all records which meet a specific criteria, insert only pieces of the matching row into another table AND update other fields in a single query?
For example:
In table 2, i have member name, date registered, and memberid. I want to move the above records into table 1 but also update the field (status) equal to 'processed'.
Note: I am also using php and pdo to connect to a mysql database.
Is this possible within a single query?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您没有指定要更新的行是否与要插入的行相同。我假设他们是：
You didn't specify whether the rows you want to update are the same as the ones you are inserting. I am assuming they are:
经过深思熟虑 - 我决定使用 ircmaxell 的建议并简单地运行多个查询。它最终不仅使事情变得更容易，而且使我能够更轻松地自定义排序。
正如他上面所说，“不要陷入越少越好的陷阱”
After much consideration - I decided to use ircmaxell's advice and simply run multiple queries. It ends up not only making things easier but allows me to customize my sorting much easier.
As he said above, "Don't get caught in the trap of less is always better"
是的：
您必须根据表结构进行调整，甚至可能写出所有字段：
值得注意的是，这假设您想要写入表 2包括已处理的变量（我可能建议使用布尔值） ？）如果你想要另一个表中的“已处理”，它会变得更加复杂。
编辑：显然 mysql 不支持 select into 所以...
Redfilters 代码可以工作
Yes:
You will have to adapt based on the table structures, perhaps even write out all the fields:
Of note, this assumes you want to write into table 2 including the processed variable (Might I suggest a boolean?) if you want the "Processed" in the other table it will get more complicated.
Edit: Apparently mysql doesn't support select into so...
Redfilters code works