Copy values from one column to another in the same table

mysql database
Lucas picture Lucas · Jan 25, 2012 · Viewed 172.8k times · Source

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?

Answer

Juicy Scripter picture Juicy Scripter · Jan 25, 2012

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, that 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

Related questions

'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More
How do I quickly rename a MySQL database (change schema name)?

The MySQL manual at MySQL covers this. Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does …

Read More
MySQL Error: : 'Access denied for user 'root'@'localhost'

$ ./mysqladmin -u root -p 'redacted' Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' How can I fix this?

Read More