Database Reference
In-Depth Information
Let's take a look at a simple example. Customer
10005
now has an e-mail
address, and so his record needs updating. The following statement performs
this update:
▼
Input
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
▼
Analysis
The
UPDATE
statement always begins with the name of the table being
updated. In this example, it is the
customers
table. The
SET
command is
then used to assign the new value to a column. As used here, the
SET
clause
sets the
cust_email
column to the specified value:
SET cust_email = 'elmer@fudd.com'
The
UPDATE
statement finishes with a
WHERE
clause that tells MariaDB which
row to update. Without a
WHERE
clause, MariaDB would update all the rows
in the
customers
table with this new e-mail address—definitely not the
desired effect.
Updating multiple columns requires a slightly different syntax:
▼
Input
UPDATE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
▼
Analysis
When updating multiple columns, only a single
SET
command is used, and
each
column = value
pair is separated by a comma. (No comma is specified
after the last column.) In this example, columns
cust_name
and
cust_email
are updated for customer
10005
.
Tip
Using Subqueries in an
UPDATE
Statement Subqueries may be used in
UPDATE
statements, enabling you to update columns with data retrieved with a
SELECT
state-
ment. Refer to Chapter 14, “Working with Subqueries,” for more information on subque-
ries and their uses.