MySQL：如何复制行，但更改一些字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2783150/
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
MySQL: How to copy rows, but change a few fields?
提问by Andrew
I have a large number of rows that I would like to copy, but I need to change one field.
我有很多行要复制，但我需要更改一个字段。
I can select the rows that I want to copy:
我可以选择要复制的行：
select * from Table where Event_ID = "120"
Now I want to copy all those rows and create new rows while setting the Event_IDto 155. How can I accomplish this?
现在，我想复制所有的行而设置创建新行Event_ID来155。我怎样才能做到这一点？
回答by dcp
INSERT INTO Table
( Event_ID
, col2
...
)
SELECT "155"
, col2
...
FROM Table WHERE Event_ID = "120"
Here, the col2, ... represent the remaining columns (the ones other thanEvent_ID) in your table.
此处， col2, ... 表示表中的其余列（Event_ID以外的列）。
回答by Alex Christodoulou
This is a solution where you have many fields in your table and don't want to get a finger cramp from typing all the fields, just type the ones needed :)
这是一个解决方案，您的表格中有许多字段，并且不想因输入所有字段而导致手指抽筋，只需输入所需的字段:)
How to copy some rows into the same table, with some fields having different values:
如何将某些行复制到同一个表中，其中某些字段具有不同的值：
- Create a temporary table with all the rows you want to copy
- Update all the rows in the temporary table with the values you want
- If you have an auto increment field, you should set it to NULL in the temporary table
- Copy all the rows of the temporary table into your original table
- Delete the temporary table
- 创建一个包含所有要复制的行的临时表
- 用你想要的值更新临时表中的所有行
- 如果您有自增字段，则应在临时表中将其设置为 NULL
- 将临时表的所有行复制到原始表中
- 删除临时表
Your code:
您的代码：
CREATE table temporary_table AS SELECT * FROM original_table WHERE Event_ID="155";
UPDATE temporary_table SET Event_ID="120";
UPDATE temporary_table SET ID=NULL
INSERT INTO original_table SELECT * FROM temporary_table;
DROP TABLE temporary_table
General scenario code:
一般场景代码：
CREATE table temporary_table AS SELECT * FROM original_table WHERE <conditions>;
UPDATE temporary_table SET <fieldx>=<valuex>, <fieldy>=<valuey>, ...;
UPDATE temporary_table SET <auto_inc_field>=NULL;
INSERT INTO original_table SELECT * FROM temporary_table;
DROP TABLE temporary_table
Simplified/condensed code:
简化/精简代码：
CREATE TEMPORARY TABLE temporary_table AS SELECT * FROM original_table WHERE <conditions>;
UPDATE temporary_table SET <auto_inc_field>=NULL, <fieldx>=<valuex>, <fieldy>=<valuey>, ...;
INSERT INTO original_table SELECT * FROM temporary_table;
As creation of the temporary table uses the TEMPORARYkeyword it will be dropped automatically when the session finishes (as @ar34z suggested).
由于临时表的创建使用TEMPORARY关键字，它将在会话结束时自动删除（如@ar34z 建议的那样）。
回答by Peter Bailey
Let's say your table has two other columns: foo and bar
假设您的表还有另外两列：foo 和 bar
INSERT INTO Table (foo, bar, Event_ID)
SELECT foo, bar, "155"
FROM Table
WHERE Event_ID = "120"
回答by Davethebfg
If you have loads of columns in your table and don't want to type out each one you can do it using a temporary table, like;
如果您的表中有大量列并且不想输入每一列，您可以使用临时表来完成，例如；
SELECT *
INTO #Temp
FROM Table WHERE Event_ID = "120"
GO
UPDATE #TEMP
SET Column = "Changed"
GO
INSERT INTO Table
SELECT *
FROM #Temp
回答by Dimitar
Hey how about to copy all fields, change one of them to the same value + something else.
嘿如何复制所有字段，将其中一个更改为相同的值 + 其他内容。
INSERT INTO Table (foo, bar, Event_ID)
SELECT foo, bar, Event_ID+"155"
FROM Table
WHERE Event_ID = "120"
??????????
？？？？？？？？？
回答by axel.becker
As long as Event_ID is Integer, do this:
只要 Event_ID 是整数，请执行以下操作：
INSERT INTO Table (foo, bar, Event_ID)
SELECT foo, bar, (Event_ID + 155)
FROM Table
WHERE Event_ID = "120"
