mysql update & insert from file

Maintaining Enrolment system is not an pleasant task. So many things must be reminded as bad documentation was supplied. Hehehe blame on myself that was too lazy to write them down.

This is just notes from me to rectify those problems above:

In order to update the data by increasing or decreasing its value, you do not need to use select then update. Instead you only need update query. See the example below:

UPDATE employee_data SET
salary = salary + 20000,
bonus = bonus - 5000
WHERE title='CEO';

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

While, inserting data from main database server to local database [in my case: I need to copy data from main uni's database into faculty's database, so my proposed solution is to read from main uni's database using php then run a script that contains command below. To make the process run automatically, I will put it as cron jobs]

Inserting data into employee_data table with employee.dat file
On Windows
1). Move the file to c:\mysql\bin.
2). Make sure MySQL is running.
3). Issue the following command
mysql employees < employee.data

On Linux
1). Migrate to the directory that contains the downloaded file.
2). Issue the following command
mysql employees < employee.data -u theUser -p
3). Enter your password.

Example of employee.dat:

INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("John", "Hagan", "Senior Programmer", 32, 4, 120000, 25000, "john_hagan@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Ganesh", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g_pillai@bignet.com");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Anamika", "Pandit", "Web Designer", 27, 3, 90000, 15000, "ana@bignet.com");

Sources from the tips above with little modification: http://www.webdevelopersnotes.com