How to Copy Column to Another Column in MySQL

MySQL is a popular database system used by millions of developers and organizations. It allows you to quickly build database applications and websites as per your requirement. Often database developers and administrators require value of one column to be copied to another column in MySQL. In this article, we will learn how to copy column to another column in MySQL.

How to Copy Column to Another Column in MySQL

We will cover several use cases to copy column to another column in MySQL. Let us say you have the following data in MySQL.

Database name: list
Table name: data

----------------------
id |number | test
----------------------
1  | 123456 | somedata
2  | 123486 | somedata1
3  | 232344 | 34
...

Let us say you want to copy the data of number column, to test column as shown below, in the same table.

Database name: list
Table name: data

-------------------
id | number | test
-------------------
1  | 123456 | 123456
2  | 123486 | 123486
3  | 232344 | 232344
...

Here is the SQL query to copy one column to another in the same table.

UPDATE data SET test=number

The above query will copy column number to test for each row. If you want to copy one column to another only for certain rows then you can specify the condition using WHERE clause. Here is a SQL query to copy column number to test only where id>5.

If you want to copy column of one table to another table then you will need to make of sub queries. For example, let us say you have two tables data1 and data2, as shown below.

Database name: list
Table name: data1

--------------
id | test
--------------
1  | somedata
2  | somedata1
3  | 34
...

Database name: list
Table name: data2

-----------
id | number 
-----------
1  | 123456
2  | 123486
3  | 232344
...

Here is a query to copy the number column in data2 table to test column in data1 table.

UPDATE data1 
    SET test = (
        SELECT number
        FROM data2
        WHERE data1.id = data2.id
    );

In the above query, we first select number column from data2 table and use it in the SET clause to set test column. For each row in data1 table, we select number column from data2 whose id column matches the id column in data1 table.

If you want to copy column to another column in a different database, you need to specify the name of the database before the table name as shown above. Let us say you have the following two tables data1 and data2 in two different databases db1 and db2 respectively.

Database name: db1
Table name: data1

--------------
id | test
--------------
1  | somedata
2  | somedata1
3  | 34
...

Database name: db2
Table name: data2

-----------
id | number 
-----------
1  | 123456
2  | 123486
3  | 232344
...

Here is a query to copy the number column in data2 table to test column in data1 table. We have highlighted the database names in bold.

UPDATE db1.data1 
    SET test = (
        SELECT number
        FROM db2.data2
        WHERE db1.data1.id = db2.data2.id
    );

Please note, for the above SQL query to work properly, the same database user needs to have access to both databases. Otherwise, you will get an error saying ‘Access Denied’.

In this article, we have learnt how to copy column to another column, whether they are in same table or different tables, or even different databases.

Often developers and administrators need to copy data across column in MySQL. The above queries can be helpful in this regard. Depending on your requirement, you can use the appropriate query to copy data from one column to another.

How to Copy Column to Another Column in MySQL

How to Copy Column to Another Column in MySQL

Related posts:

Leave a Reply Cancel reply