Using TRUNCATE

The TRUNCATE statement allows us to delete all the rows from a table. For example:

TRUNCATE TABLE employee;

This query would delete all the employees from the employee table. This is faster than a DELETE statement because it works by dropping the table and re-creating it empty. One thing to bear in mind is that TRUNCATE is not transaction safe.

Using UPDATE

We can use the UPDATE SQL statement to change rows already stored in the database. For example, imagine that one of our employees changes jobs:

update employee
set job='DBA'
where employeeID='6651';

This statement changes the value of the job column for employee number 6651.

The general form of the UPDATE statement from the MySQL manual is as follows :

UPDATE [LOW_PRIORITY] [IGNORE]

tbl_name

SET

col_name1=expr1

[,

col_name2=expr2

...]
    [WHERE

where_definition

]
    [ORDER BY ...]
    [LIMIT

rows

]

or

UPDATE [LOW_PRIORITY] [IGNORE]

tbl_name

[,

tbl_name

...]
    SET

col_name1=expr1

[,

col_name2=expr2

...]
    [WHERE

where_definition

]

The UPDATE statement is similar in many respects to the DELETE statement.

We can use an optional WHERE clause to update particular rows or leave it off to update all rows. Again, you can fall into the trap of forgetting to specify a WHERE clause ”I remember one project when a foolish colleague typed something along these lines:

update user
set password='test';

This again highlights the usefulness of the --i-am-a- dummy mysql option, particularly if you are forced to work with dummies.

The second version of the UPDATE statement listed previously is a multi-table update. This works similarly to the multi-table deletes we looked at before. Note that only the columns you specifically list in the SET clause will be updated.

We have seen all the other clauses of the UPDATE statement before. The LOW_PRIORITY and IGNORE clauses work the same way as they do in INSERT . The ORDER BY and LIMIT clauses work the same way they do in DELETE .

Uploading Data with LOAD DATA INFILE

The LOAD DATA INFILE command allows you to bulk insert data from a text file into a single table without having to write INSERT statements. For example, we could have loaded the data in the department table using this technique as follows . Listing 5.2 shows the contents of a data file containing department information.

Listing 5.2 department_infile.txt

42     Finance
128    Research and Development
NULL   Human Resources
NULL   Marketing

This file is in the default LOAD DATA INFILE format, with each row listed on a separate line with tabs between column values. (This is configurable and we will see how in a minute.)

We can load this information into the department table with the following LOAD DATA INFILE statement:

load data local infile 'department_infile.txt'
into table department;

This facility is particularly useful for converting data from another database format, spreadsheet, or CSV (comma-separated values) file.

The LOAD DATA INFILE statement requires the FILE privilege ”see Chapter 11, "Managing User Privileges," for further information, especially if you are having trouble running this command. The privilege to perform this is often restricted for good security reasons ”to stop people from loading in /etc/passwd, for example.

The general form of the LOAD DATA INFILE statement is as follows:

LOAD DATA [LOW_PRIORITY  CONCURRENT] [LOCAL] INFILE '

fileName.txt

'
    [REPLACE  IGNORE]
    INTO TABLE

tbl_name

[FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\' ]
    ]
    [LINES TERMINATED BY '\n']
    [IGNORE

number

LINES]
    [(

col_name

,...)]

The optional clauses are as listed here:

The LOW PRIORITY clause works the same way it does in the INSERT statement by waiting for other clients to stop reading from the table. CONCURRENT , on the other hand, allows other clients to read from the table while the bulk insert is going on.
In our example, we specified the optional keyword LOCAL , meaning that the data file is on the client machine. If this is not specified, MySQL will look for the infile on the server.
If you have key clashes while inserting data, REPLACE and IGNORE provide two methods for dealing with this. Specifying REPLACE tells MySQL to replace the old row with the new row, while IGNORE tells MySQL to keep the old row.
The FIELDS and LINES clauses specify how the data in the infile is laid out. The values in the general form are the defaults ”each row on a new line, column values separated by tabs. We can also enclose column values in quotes and use the backslash character to escape any special characters (like single quotes) that might confuse MySQL.
The IGNORE number LINES clause tells MySQL to ignore the first number lines in the infile.
The final clause allows you to specify that you only want to read data into some of the table's columns .

A common format to receive data in is CSV or comma-separated values. Many programs can read and write files of this type, but one notable example is Microsoft Excel. Listing 5.3 shows a small CSV file saved from Excel.

Listing 5.3 new_programmers.csv

Name,Job,DepartmentID

Julia Lenin,Programmer,128
Douglas Smith,Programmer,128
Tim O'Leary,Programmer,128

We can load this data into the employee table with the following query:

load data infile 'e:\new_programmers.csv'
into table employee
fields terminated by ','
lines terminated by '\n'
ignore 2 lines
(name, job, departmentID);

You can see that we have used more options to load this data than we did when the data was in the default format. A few points are worth noting:

Because we have used a Windows/DOS-style path that includes a backslash, we need to escape the backslash. Our path therefore became 'e:\\new_programmers.csv' .
It possibly goes without saying that the fields in a CSV file are terminated by commas, but we need to specify it.
We do not need to specify that lines are terminated by a newline character, but we have chosen to.
This file has a header, so the first two lines do not contain data and should be ignored.
The data in this file does not contain employeeIDs, so to allocate the three columns of data into the four columns in the database, we need to specify what columns (in order) the data will be mapped to. In this case, we have specified (name, job, departmentID) .