Inserting Data - Learning MySQL and MariaDB - page 128

ALTER TABLE bird_families
DROP COLUMN cornell_bird_order ;
DROP TABLE cornell_birds_families_orders ;
That set of examples was complicated, so don't be discouraged if you were confused by it.
In time, you will be constructing more complex SQL statements on your own. In fact, you
will come to look at what I did here and realize that I could have performed the same
tasks in fewer steps. For now, I wanted to show you the power of MySQL and MariaDB,
as well as their communities. I mention the communities because in the MySQL and
MariaDB communities, you can sometimes find tables with data like this that you can
download for free and then manipulate for your own use, thus saving you plenty of work
and taking some of the ever pesky tediousness out of database management. There are
other methods for bulk importing data, even when it's not in a MySQL table.They're
covered in Chapter15 .
Replacing Data
When you're adding massive amounts of data to an existing table and you're using the
multiple-row syntax, you could have a problem if one of the fields you're importing gets
inserted into a key field in the table, as in the preceding example with the
bird_families table. In that example, the scientific_name column was a key
field, set to UNIQUE so that there is only one entry in the birds_families table for
each bird family. When MySQL finds a duplicate key value while runningan INSERT
statement, an error is generated and the entire SQL statement will be rejected. Nothing
will be inserted into the table.
You would then have to edit the INSERT statement, which might be lengthy, to remove
the duplicate entry and run the statement again. If there are many duplicates, you'd have
to run the SQL statement many times, watch for error messages, and remove duplicates
until it's successful. We avoided this problem in the previous example byusing the
IGNORE option with the INSERT statement. It tells MySQL to ignore the errors, not in-
sert the rows that are duplicates, and insert the ones that aren't.
There may be times, though, when you don't want to ignore the duplicate rows, but re-
place duplicate rows in the table with the new data. For instance, in the UPDATE example
in the previous section, we have newer and better information, so we prefer to overwrite
duplicate rows. In situations such as this, instead of using INSERT , you could usethe
REPLACE statement. With it, new rows of data will be inserted as they would with an
INSERT statement. Any rows with the same key value (e.g., same scientific_name