How to copy data from one field to another on every row in MySQL?

MySQLMySQLi Database

To copy data from one field to another on every row, use the UPDATE command.

Let us first create a table −

mysql> create table DemoTable
   (
   StudentId int,
   StudentFirstName varchar(20),
   StudentMarks int default 0
   );
Query OK, 0 rows affected (0.49 sec)

Following is the query to insert records in the table using insert command −

mysql> insert into DemoTable(StudentId,StudentFirstName) values(89,'Larry');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable(StudentId,StudentFirstName) values(35,'Robert');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(StudentId,StudentFirstName) values(48,'Chris');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable(StudentId,StudentFirstName) values(78,'David');
Query OK, 1 row affected (0.61 sec)

Following is the query to display records from the table using select command −

mysql> select *from DemoTable;

This will produce the following output −

+-----------+------------------+--------------+
| StudentId | StudentFirstName | StudentMarks |
+-----------+------------------+--------------+
| 89        | Larry            | 0            |
| 35        | Robert           | 0            |
| 48        | Chris            | 0            |
| 78        | David            | 0            |
+-----------+------------------+--------------+
4 rows in set (0.00 sec)

Following is the query to copy data from one field to another on every row. Here, we are copying all the values of StudentId to StudentMarks −

mysql> update DemoTable set StudentMarks=StudentId;
Query OK, 4 rows affected (0.34 sec)
Rows matched: 4 Changed: 4 Warnings: 0

Let us display all records from the table to check all rows have been updated or not −

mysql> select *from DemoTable;

This will produce the following output −

+-----------+------------------+--------------+
| StudentId | StudentFirstName | StudentMarks |
+-----------+------------------+--------------+
| 89        | Larry            | 89           |
| 35        | Robert           | 35           |
| 48        | Chris            | 48           |
| 78        | David            | 78           |
+-----------+------------------+--------------+
4 rows in set (0.00 sec)
raja
Published on 26-Apr-2019 11:17:54
Advertisements