How can we upload the changed value, rather than written in a text file, of column(s) while importing that text file into MySQL table?

Suppose if we want to upload the changed value rather than the value written in a text file then we need to use user variables along with the SET command. It can be understood with the help of the following example −

Example

Suppose we are having the following data in ‘A.txt’ −

105,Chum,USA,11000
106,Danny,AUS,12000

But we want to upload the value of salary after adding 500 to it at the time of importing it without changing the value of salary in a text file then it can be done with the help of the following query by using user variable along with SET option −

mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee11_tbl FIELDS TERMINATED BY ',' (id,name,country,@salary) SET salary = @salary + 500;
Query OK, 2 rows affected (0.21 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> Select * from employee11_tbl;
+------+----------------+----------+--------+
| Id   | Name           | Country  | Salary |
+------+----------------+----------+--------+
| 105  | Chum           | USA      |  11500 |
| 106  | Danny          | AUS      |  12500 |
+------+----------------+----------+--------+
2 rows in set (0.00 sec)

From the above result set, it is clear that MySQL uploaded the data into the table after adding 500 to the value of salary.

Govinda Sai

e

Updated on: 06-Feb-2020

66 Views

Kickstart Your Career

Get certified by completing the course

Get Started