Page 505 - Microsoft Office Excel 2003 Programming Inside Out

Excel and the Structured Query Language

The Update Statement

The Update statement changes the values for one or more columns in the table. Here is
its syntax:

Update <table>
Set <column> = <value> [, <column> = <value>]…
[Where <expression>]

Where <table> is the name of the table that you wish to update, <column> is the name of
the column in the table, <value> is the new value that will be stored in the column, and
<expression> is true for all the rows that should be updated.
The Update statement provides a method that permits you to change the value of one or
more columns in the table. You must explicitly specify each column that you wish to change,
along with the column’s new value. Also remember that the Update statement can include a
Where clause. This Where clause is identical to the one you use in the Select statement and it
works just like you would expect. Only the rows that match the expression in the Where
clause will be updated—the other rows will remain unchanged.

Tip Limit Your Exposure
If your Update statement doesn’t have a Where clause, ask yourself “Do I really want to
change all the rows in the table?” If your answer is no, you had better double-check the
statement before you try to execute it.

In this example, the Where clause is used to identify all the rows that contain a Null value in
Chapter 22
the DateUpdated field. The DateUpdated column is then set to ‘01-Jan-2003’.

Update Customers
Set DateUpdated = ’01-Jan-2003’
Where DateUpdated Is Null

The Delete Statement

The Delete statement removes rows from a table. Here is its syntax.
Delete From <table>
[Where <expression>]
Where <table> is the name of the table that you wish to update, and <expression> is true for
all the rows that should be updated.
You can use the Delete statement to remove one or more rows from a table. Simply specifying
Delete From Customers will delete every row from the Customers table. However, this isn’t a
good idea unless you really want an empty table. Using the same Where clause in the Select
statement lets you limit the number of rows deleted.

479
Part 6: Excel and the Outside World: Collaborating Made Easy