Datasheet

This query would delete all the rows from a table called ProductA that didn’t have ProductName values
that started with the letter
A. Here’s another example:
DELETE FROM ProductCategory
WHERE ProductCategoryId = ‘3bd514c0-97a1-11da-a72b-0800200c9a66’
Because ProductCategory.ProductCategoryId is a primary key column that doesn’t allow duplicate
values, this command will delete zero or one row from the
ProductCategory table, where the row that
will be deleted has a
ProductCategoryId column containing the GUID 3bd514c0-97a1-11da-a72b-
0800200c9a66
.
Updating Data
One way to update data in a database table is to delete a row and then add it again with slightly differ-
ent data. However, that may be difficult or perhaps impossible to do if, for example, the table includes
an identity column and you were required to keep the value of that column constant. Removing and
then adding a row might also break relationships between rows, and the RDBMS may be configured to
prevent you from doing this. In addition, this could cause conflicts and/or errors where multiple users
access the database simultaneously.
Because of all this, the SQL specification includes another useful keyword to update data in existing
rows:
UPDATE. The syntax of an update statement is as follows:
UPDATE [Table] SET [Column Modification(s)] WHERE [Filter]
[Table] is the table containing the rows that you want to modify, [Column Modification(s)] is one
or more comma-separated modifications to the rows in the table, and
[Filter] filters the rows in the
table that the update should apply to. As with previous queries, the where clause is optional.
Each column modification specification takes the following form:
[Column] = [Value]
[Column] is the name of the column to modify and [Value] is the value to replace the existing values
in that column with. The value specified may be a simple literal value, or it may involve a calculation. If
using a calculation, you can include the current value of a column in that calculation. For example:
UPDATE Product SET ProductCost = ProductCost * 1.1
This query would have the effect of increasing the cost of all products in the Product table by 10 per-
cent, using the standard mathematical multiplication operator
*.
As with delete queries, judicious use of the where clause may be required to restrict the rows where
modifications should take place. Also, specifying a value for the primary key of a row in the where
clause makes it possible to edit the content of individual rows.
Manipulating Databases
As well as being able to manipulate the data within databases, the SQL language includes all the com-
mands you might need to manipulate database objects, including databases, stored procedures, tables,
and so on.
28
Chapter 1
44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 28