Chapter 5 ■ MySQL priMer
Notice I specified the columns before the data for the row. This is necessary whenever you want to
insert data for fewer columns than what the table contains. More specifically, leaving the column list off
means you must supply data (or NULL) for all columns in the table. Also, the order of the columns listed can
be different from the order they are defined in the table. Leaving the column list off will result in the ordering
the column data based on how they appear in the table.^12
You can also insert several rows using the same command by using a comma-separated list of the row
values, as shown here:
INSERT INTO plant_monitoring.plants (plant_name, sensor_value) VALUES ('flowers in
bedroom1', 0.301), ('weird plant in kitchen', 0.677), ('fern on deck', 0.430);
Here I’ve inserted several rows with the same command. Note that this is just a shorthand mechanism
and, except for automatic commits, no different than issuing separate commands.^13
Changing Data
There are times when you want to change or update data. You may have a case where you need to change
the value of one or more columns, replace the values for several rows, or correct formatting or even scale of
numerical data. To update data, we use the UPDATE command.
You can update a particular column, update a set of columns, perform calculations one or more
columns, and more. I do not normally have much need to change data in an IOT solution, but sometimes in
the case of mistakes in sensor-reading code or similar data entry problems, it may be necessary.
What may be more likely is you or your users will want to rename an object in your database. For
example, suppose we determine the plant on the deck is not actually a fern but was an exotic flowering
plant.^14 In this case, we want to change all rows that have a plant name of “fern on deck” to “flowers on deck.”
The following command performs the change:
UPDATE plant_monitoring.plants SET plant_name = 'flowers on deck' WHERE plant_name =
'fern on deck';
Notice the key operator here is the SET operator. This tells the database to assign a new value to the
column(s) specified. You can list more than one set operation in the command.
Notice I used a WHERE clause here to restrict the UPDATE to a particular set of rows. This is the same WHERE
clause as you saw in the SELECT statement, and it does the same thing; it allows you to specify conditions that
restrict the rows affected. If you do not use the WHERE clause, the updates will apply to all rows.
■Caution Don’t forget the WHERE clause! issuing an UPDATE command without a WHERE clause will affect all
rows in the table!
(^12) If you’re a relational database expert like me, ordering concepts like this in database systems and especially SQL makes
my skin crawl. So much for the “unordered” concept!
(^13) See the online reference manual for additional conditions and differences of the multiple row insert command.
(^14) Hey, it happens. While this is fictional, my wife and I discovered a plant we thought was one thing turned out to be
another when it started to bloom.