mysql 复制一些行并更新一列

发布于 2024-10-22 02:32:17 字数 553 浏览 1 评论 0原文

我读了标题相似的问题，但它与我的问题不符。

我有这个表

Robot_Minions
id | type   | id_robot_master
1  | catbot | 15
2  | dogbot | 15
3  | batbot | 15

我想做的是复制 Robot_Master 15 的所有 Robot_Minons 并将它们分配给 Robot_Master 16。

所以最终结果应该看起来像

Robot_Minions
id | type   | id_robot_master
1  | catbot | 15
2  | dogbot | 15
3  | batbot | 15
4  | catbot | 16
5  | dogbot | 16
6  | batbot | 16

我能想到的一种方法是首先选择要复制的行，然后循环遍历它们并运行 INSERT blah 然后 UPDATE blah WHERE id=last insert id。但这是 1+2x 查询。有没有更好的方法，最好是作为一个查询？

原文

i read the question with a similar title but it doesn't match my problem.

I have this table

Robot_Minions
id | type   | id_robot_master
1  | catbot | 15
2  | dogbot | 15
3  | batbot | 15

What I want to do is copy all the Robot_Minons of Robot_Master 15 and assign them to Robot_Master 16.

So the end result should look like

Robot_Minions
id | type   | id_robot_master
1  | catbot | 15
2  | dogbot | 15
3  | batbot | 15
4  | catbot | 16
5  | dogbot | 16
6  | batbot | 16

One way I can think of to do it is to first select the rows to be copied, then loop through them and run an INSERT blah then UPDATE blah WHERE id=last insert id. But this is 1+2x queries. Is there a better way, ideally as one query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

扛刀软妹 2024-10-29 02:32:17

如果您已经知道要为其分配 Minion 的 robots_master 的 ID，则可以使用以下查询：

INSERT INTO Robot_minions (type,id_robot_master)
SELECT type, '<new robot_master_id>' 
FROM Robot_minions
WHERE id_robot_master = '<old robot_master>'

这将选择属于的 Minion，然后插入最终结果集使用进入 Robot_minions

if you already know the id of the robot_master you wish to assign the minions to you can use the following query:

INSERT INTO Robot_minions (type,id_robot_master)
SELECT type, '<new robot_master_id>' 
FROM Robot_minions
WHERE id_robot_master = '<old robot_master>'

this will select the minions that belong to the <old robot_master> and then insert the end resultset into Robot_minions with the <new robot_master_id>

回复收藏 0 原文

~没有更多了~