MySQL 将值从一列复制到同一表中的另一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9001939/
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
Copy values from one column to another in the same table
提问by Lucas
How can I make a copy values from one column to another? I have:
如何将值从一列复制到另一列？我有：
Database name: list
number | test
123456 | somedata
123486 | somedata1
232344 | 34
I want to have:
我希望有：
Database name: list
number | test
123456 | 123456
123486 | 123486
232344 | 232344
What mysql query should I have?
我应该有什么 mysql 查询？
回答by Juicy Scripter
Short answer for the code in question is:
相关代码的简短回答是：
UPDATE `table` SET test=number
Here tableis the table name and it's surrounded by grave accent (aka back-ticks `) as this is MySQL convention to escape keywords(and TABLEis a keyword in that case).
这table是表名，它被重音符（又名反引号`）包围，因为这是 MySQL转义关键字的约定（TABLE在这种情况下是关键字）。
BEWARE, that this is pretty dangerous query which will wipe everything in column testin every row of your table replacing it by the number(regardless of it's value)
请注意，这是非常危险的查询，它将擦除test表每一行列中的所有内容，并将其替换为number（无论其值如何）
It is more common to use WHEREclause to limit your query to only specific set of rows:
更常见的是使用WHERE子句将您的查询限制为特定的一组行：
UPDATE `products` SET `in_stock` = true WHERE `supplier_id` = 10
回答by Czechnology
UPDATE `table_name` SET `test` = `number`
You can also do any mathematical changes in the process or use MySQL functions to modify the values.
您还可以在过程中进行任何数学更改或使用 MySQL 函数来修改值。
回答by juergen d
try this:
尝试这个：
update `list`
set `test` = `number`
回答by zzapper
BEWARE: Order of update columns is critical
注意：更新列的顺序很重要
GOOD: What I want saves existing Value of Status to PrevStatus
好：我想要将现有的状态值保存到 PrevStatus
UPDATE Collections SET PrevStatus=Status, Status=44 WHERE ID=1487496;
BAD: Status & PrevStatus both end up as 44
BAD：Status 和 PrevStatus 最终都为 44
UPDATE Collections SET Status=44, PrevStatus=Status WHERE ID=1487496;
回答by Jigneshsinh Rathod
try following:
尝试以下：
UPDATE `list` SET `test` = `number`
it creates copy of all values from "number" and paste it to "test"
它从“数字”创建所有值的副本并将其粘贴到“测试”
回答by nitinr708
Following worked for me..
以下为我工作..
- Ensure you are not using Safe-mode in your query editor application. If you are, disable it!
- Then run following sql command
- 确保您没有在查询编辑器应用程序中使用安全模式。如果是，请禁用它！
- 然后运行以下sql命令
for a table say, 'test_update_cmd', source value column col2, target value column col1 and condition column col3: -
UPDATE test_update_cmd SET col1=col2 WHERE col3='value';
对于表说，'test_update_cmd'，源值列 col2，目标值列 col1 和条件列 col3：-
UPDATE test_update_cmd SET col1=col2 WHERE col3='value';
Good Luck!
祝你好运！
回答by Karan Kumar Mahto
you can do it with Procedure also so i have a procedure for this
你也可以用程序来做，所以我有一个程序
DELIMITER $$
CREATE PROCEDURE copyTo()
BEGIN
DECLARE x INT;
DECLARE str varchar(45);
SET x = 1;
set str = '';
WHILE x < 5 DO
set str = (select source_col from emp where id=x);
update emp set target_col =str where id=x;
SET x = x + 1;
END WHILE;
END$$
DELIMITER ;
