Database Reference
In-Depth Information
+----------+------------+-----------+
| human_id | name_first | name_last |
+----------+------------+-----------+
| 3 | Rusty | Johnson |
+----------+------------+-----------+
That worked just fine. It's easy to use the
UPDATE
statement, especially when you know
the identification number of the key column for the one row you want to change. Let's
suppose that two of our members who are married women have asked us to change their
title from
Mrs.
to
Ms.
(this information is contained in an enumerated column called
formal_title
). After running a
SELECT
statement to find their records, we see that
their
human_id
numbers are 24 and 32. We could then execute the following
UPDATE
statement in MySQL:
UPDATE
humans
SET
formal_title
=
'Ms.'
WHERE
human_id
IN
(
24
,
32
);
Things get slightly more complicated when you want to change more than one row, but
it's still easy if you know the key values. In this example, we used the
IN
operator to list
the
human_id
numbers to match specific rows in the table.
Suppose that after updating the title for the two women just shown, we decide that we
want to make this change for all married women in the database, to get with the modern
times. We would use the
UPDATE
statement again, but we'll have to modify the
WHERE
clause. There may be too many women with the
formal_title
of
Mrs.
in the table to
manually enter the
human_id
for all of them. Plus, there's an easier way to do it. First,
let's see how the
formal_title
column looks now:
SHOW FULL COLUMNS
FROM humans
LIKE 'formal_title' \G
*************************** 1. row ***************************
Field: formal_title
Type: enum('Mr.','Miss','Mrs.','Ms.')
Collation: latin1_bin
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
