mySql 将行复制到同一个表中，并更改键值（不覆盖现有）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6549695/
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 copy rows into same table with key value changed (not overwriting existing)
提问by Muleskinner
How do I copy a selection of rows from a mySql table and insert with just the key value changed. Can I do a select and insert in same query?
如何从 mySql 表中复制选择的行并仅在更改键值的情况下插入。我可以在同一个查询中进行选择和插入吗？
To be precise, what I want would look like this:
准确地说，我想要的是这样的：
Table cols, ID and ISO3 are keys:
表列、ID 和 ISO3 是键：
+----+------+-------------------------+
| ID | ISO3 | Text |
+----+------+-------------------------+
| 1 | ENU | A text in english |
| 2 | ENU | Another text in english |
| 3 | ENU | bla bla |
| 1 | JPN | 与えられた枠 |
+----+------+-------------------------+
After the insert I want my table to look like this:
插入后，我希望我的表看起来像这样：
+----+------+---------------------------+
| ID | ISO3 | Text |
+----+------+---------------------------+
| 1 | ENU | A text in english |
| 2 | ENU | Another text in english |
| 3 | ENU | bla bla |
| 1 | JPN | 与えられた枠 |
| 2 | JPN | Another text in english |
| 3 | JPN | bla bla |
+----+------+---------------------------+
回答by Daniel Gruszczyk
INSERT INTO your_table (ID, ISO3, TEXT)
SELECT ID, 'JPN', TEXT
FROM your_table
WHERE ID IN ( list_of_ id's )
If you want to change a value in one cell, just hard-type the value instead of selecting from table (like I did with 'JPN').
如果您想更改一个单元格中的值，只需硬输入该值而不是从表中选择（就像我对“JPN”所做的那样）。
回答by GordonM
If your key is auto_increment, and you have a table structure like primary_key | col1 | col2 | col3then you can do something along the lines of
如果您的键是 auto_increment，并且您有一个表结构，primary_key | col1 | col2 | col3那么您可以按照以下方式做一些事情
INSERT INTO table (
col1, col2, col3
) SELECT col1, col2, col3
FROM table
WHERE primary_key IN(1, 2, 45, 54, 774, 4434 /* etc */);
回答by ABDUL JAMAL
you can do
你可以做
insert into your_table (SELECT field1, field2,(changed_value_for_field3) as field3 FROM your_table where your_condition);
