Database Reference
In-Depth Information
Setting Columns
If you want toprocess the values found in a field before loading them into a column in a
table, you can use the
SET
clause of the
LOAD DATA INFILE
statementto do that. In
the previous examples, we used
SUBSTRING()
in the
INSERT INTO...SELECT
statement to eliminate common names contained within parentheses from the
family
column in the
clements_list_import
table. Let's try loading the data again, but
this time let's get rid of those common names when they're loaded into the
family
column. Delete and load the data again by running these two SQL statements:
DELETE FROM rookery.clements_list_import;
LOAD DATA INFILE '
/tmp/Clements-Checklist-6.9-final.csv
'
INTO TABLE rookery.clements_list_import
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES
(id, change_type, @niente, @niente,
scientific_name, english_name,
@niente, bird_order, @family, @niente,
@niente, @niente, @niente, @niente,
@niente, @niente, @niente, @niente, @niente)
SET family = SUBSTRING(@family, 1, LOCATE(' (', @family) );
This is the same as the previous
LOAD DATA INFILE
statement, except that we are
storing the family name in a variable called
@family
and we added the
SET
clause. This
clause sets the value of columns in the table into which data is loaded. Here we are setting
the value of the
family
column to the value returnedby
SUBSTRING()
, which is ex-
tracting a substring from the
@family
variable. Let's see how well that did by selecting
just one of the new species, the Treehunter bird:
SELECT * FROM rookery.clements_list_import
WHERE change_type='new species'
AND english_name LIKE '%Treehunter%' \G
*************************** 1. row ***************************
id: 13864
scientific_name: Cichlocolaptes mazarbarnetti
english_name: Cryptic Treehunter
family: Furnariidae
bird_order: Passeriformes
change_type: new species
We can see here that the data is in the correct columns. In addition, the parenthetical text
listing common names of birds in the family has been removed. If we want, we can run
