Copy values from one column to another in the same table
MysqlDatabaseMysql Problem Overview
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 Solutions
Solution 1 - Mysql
Short answer for the code in question is:
UPDATE `table` SET test=number
Here table
is the table name and it's surrounded by grave accent (aka back-ticks `) as this is MySQL convention to escape keywords (and TABLE
is a keyword in that case).
BEWARE!
This is pretty dangerous query which will wipe everything in column test
in every row of your table replacing it by the number
(regardless of it's value)
It is more common to use WHERE
clause to limit your query to only specific set of rows:
UPDATE `products` SET `in_stock` = true WHERE `supplier_id` = 10
Solution 2 - Mysql
UPDATE `table_name` SET `test` = `number`
You can also do any mathematical changes in the process or use MySQL functions to modify the values.
Solution 3 - Mysql
try this:
update `list`
set `test` = `number`
Solution 4 - Mysql
BEWARE : Order of update columns is critical
GOOD: What I want saves existing Value of Status to PrevStatus
UPDATE Collections SET PrevStatus=Status, Status=44 WHERE ID=1487496;
BAD: Status & PrevStatus both end up as 44
UPDATE Collections SET Status=44, PrevStatus=Status WHERE ID=1487496;
Solution 5 - Mysql
try following:
UPDATE `list` SET `test` = `number`
If list
is table name and test
and number
are columns
it creates copy of all values from "number" and paste it to "test"
Solution 6 - Mysql
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
> 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';
Good Luck!
Solution 7 - Mysql
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 ;