Database Reference
In-Depth Information
| 101 | The Catcher of the Rye | 1 |
| 102 | My Antonia | 1 |
+---------+------------------------+--------+
Thanks to our update, we get two rows back this time, where the rows have a status of act-
ive. If we execute the
UPDATE
statement again, but for a different
book_id
, we can
change the topic,
The Catcher in the Rye
to inactive:
UPDATE books SET status = 0 WHERE book_id = 101;
SELECT * FROM books WHERE status = 0;
+---------+------------------------+--------+
| book_id | title | status |
+---------+------------------------+--------+
| 100 | Heart of Darkness | 0 |
| 101 | The Catcher of the Rye | 0 |
+---------+------------------------+--------+
Let's enter one more
UPDATE
statement so you can see how to do more with just one
statement. As I mentioned earlier, the title of this topic is not correct. It's not The Catcher
of the Rye. The correct title is The Catcher in the Rye. Let's change that text in the
title
column, while simultaneously setting the value of
status
back to
1
. We could do this
with two SQL statements, but let's do it in one like so:
UPDATE
books
SET
title
=
'The Catcher in the Rye'
,
status
=
1
WHERE
book_id
=
101
;
Notice that we've given the same syntax as before with the
UPDATE
statement, but we've
given two pairs of columns and values to set. That's easier than entering the
UPDATE
statement twice. It also saves some network traffic when communicating with a server on
another continent.
A Little Complexity
Let's increase the pace a little. Let's create another table and insert a couple of rows of
data in it. Enter these two SQL statements from within the
mysql
client:
CREATE TABLE
status_names
(
status_id
INT
,
status_name
CHAR
(
8
));
INSERT INTO
status_names
VALUES
(
0
,
'Inactive'
), (
1
,
'Active'
);
Now we've created the table
status_names
, but with only two columns.The
CREATE
TABLE
statement is similar to the one we used to create the first table. There's one differ-
