When importing a text file into a MySQL table, how can I upload the changed values ​​of a column instead of writing to the text file?

Suppose if we want to upload the changed value instead of the value written in the text file, then we need to use user variables with the SET command. It can be understood through the following example -

Example

Suppose we have the following data in "A.txt"-

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

But we want to upload after adding 500 while importing salary value without changing the salary value in the text file, then it can be done by using user variables and with the help of the following query using the 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)

As can be seen from the above result set, MySQL is in salary Add 500 to the value and then upload the data to the table.

The above is the detailed content of When importing a text file into a MySQL table, how can I upload the changed values ​​of a column instead of writing to the text file?. For more information, please follow other related articles on the PHP Chinese website!