Updating Data
Now that you have a handle on inserting data,
let’s look at how the UPDATE statement works. A
simple UPDATE statement typically consists of three
parts:
UPDATE Part1
SET Part2
WHERE Part3
Part1 is where you specify the target table. Part2 is
where you specify the columns that are to be changed,
along with the new data for each column. Part3 is
optional but in most cases essential. This is where you
specify a filter using the WHERE clause. If you don’t
specify a WHERE clause, you’ll update every single
record in the table. For example, the query
UPDATE MyTable
SET CreationDate = ‘2007-09-23’
updates the CreationDate value for each record in
MyTable, as Figure 3 shows. In Figure 3, note that the
CreationDate value is 2007-09-23 00:00:00.000 and not
2007-09-23 as specified in the query. Because the data
type of the CreationDate column is defined as datetime
and the query doesn’t specify a time portion, SQL Server
assumes you mean midnight and adds 00:00:00.000.
Now let’s use a WHERE clause to modify the
ObjectName and CreationDate columns for a specific
record:
UPDATE MyTable
SET ObjectName = ‘PartyTime’,
CreationDate = ‘1999-12-31
23:00:00’
WHERE TableID = 1
As Figure 4 shows, only the first record (specified
by WHERE TableID = 1) is updated with the new
table name of PartyTime and a new creation date of
1999-12-31 23:00:00.
Deleting Data
The command that every DBA fears will fall into the
wrong hands is the DELETE statement. Although it
can be misused, it serves an important role when using
queries to modify data in tables. A basic DELETE
statement typically consists of two parts:
DELETE Part1
WHERE Part2
Part1 is where you specify the target table. Part2 is
where you specify a filter using the WHERE clause.
Like the WHERE clause in an UPDATE statement,
the WHERE clause in a DELETE statement is
optional but usually essential. If you don’t include a
WHERE clause and filter, you’ll delete all the records
in the specified table.
Let’s say you want to delete the records with a
creation date older than September 22, 2007 from MyTables. The query
DELETE MyTable
WHERE CreationDate > ‘2007-09-22’
will do just that, as Figure 5 shows.
You need to be careful when modifying data with
either the DELETE or UPDATE statement. Always
include a WHERE clause unless you’re positive you
want to affect all the records in a table. If you’re unsure
of how many records you’ll be affecting, try running
a SELECT statement with the same WHERE clause.
Instead of specifying columns in the SELECT statement,
you can specify COUNT (*), which will return a
count of the rows that would be affected. For example,
to check the effect of the last DELETE statement, you
could run
SELECT COUNT (*) FROM MyTable
WHERE CreationDate > ‘2007-09-22’
If the result set is in the millions, chances are you need
to refine your WHERE clause. I’ll cover the use of the
COUNT function in the next lesson, so don’t fret if you
can’t get it to work.
Explore on Your Own
Once you’re comfortable in modifying the
data in MyTable, you might try running
INSERT, UPDATE, and DELETE
statements against tables in an existing
database on a nonproduction server. To
do so, copy the code in ExistingDatabaseQuery.
sql (which is in the 98105
.zip file) in your query window, replace
MyDB in the first line to the name of
your existing database, and run the query.
Afterward, you’ll be able to customize
the sample INSERT, UPDATE, and
DELETE statements to your environment
and run them. By doing so, you’ll
learn how to better use these statements.
You might even find data that you didn’t
know existed.
End of Article
Prev. page
1
[2]
next page -->
