</BLOCKQUOTE>

<P>Some database systems provide an extension to the standard <TT>UPDATE</TT> syntax.
SQL Server's Transact-SQL language, for instance, enables programmers to update the
contents of a table based on the contents of several other tables by using a <TT>FROM</TT>
clause. The extended syntax looks like this:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">UPDATE table_name
SET columnname1 = value1
[, columname2 = value2]...
FROM table_list
WHERE search_condition
</FONT></PRE>
<H4><FONT COLOR="#000077">Example 8.6</FONT></H4>
<P>Here's an example of the extension:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE COLLECTION</B>
  2  <B>SET WORTH = WORTH * 0.005;</B>
</FONT></PRE>
<P>that changes the table to this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> SELECT * FROM COLLECTION;</B>

ITEM                     WORTH REMARKS
-------------------- -------- ----------------------------
NBA ALL STAR CARDS       2.775 SOME STILL IN BIKE SPOKES
MALIBU BARBIE            2.775 TAN NEEDS WORK
STAR WARS GLASS          2.775 HANDLE CHIPPED
LOCK OF SPOUSES HAIR     2.775 HASN'T NOTICED BALD SPOT YET
SUPERMANS CAPE           2.775 TUGGED ON IT
STRING                   2.775 SOME DAY IT WILL BE VALUABLE

6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This syntax is useful when the contents of one table need to be updated following
the manipulation of the contents of several other tables. Keep in mind that this
syntax is nonstandard and that you need to consult the documentation for your particular
database management system before you use it.</P>
<P>The <TT>UPDATE</TT> statement can also update columns based on the result of an
arithmetic expression. When using this technique, remember the requirement that the
data type of the result of the expression must be the same as the data type of the
field that is being modified. Also, the size of the value must fit within the size
of the field that is being modified.</P>
<P>Two problems can result from the use of calculated values: truncation and overflow.<I>
Truncation </I>results when the database system converts a fractional number to an
integer, for instance. <I>Overflow </I>results when the resulting value is larger
than the capacity of the modified column, which will cause an error to be returned
by your database system.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Some database systems handle the
	overflow problem for you. Oracle7 converts the number to exponential notation and
	presents the number that way. You should keep this potential error in mind when using
	number data types. 
<HR>
</P>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>If you update a column(s) and notice
	an error after you run the update, issue the <TT>ROLLBACK</TT> command (as you would
	for an incorrect insert) to void the update. See Day 11 for more on the <TT>ROLLBACK</TT>
	command. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">The DELETE Statement</FONT></H2>
<P>In addition to adding data to a database, you will also need to delete data from
a database. The syntax for the <TT>DELETE</TT> statement is</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">DELETE FROM tablename
WHERE condition
</FONT></PRE>
<P>The first thing you will probably notice about the <TT>DELETE</TT> command is
that it doesn't have a prompt. Users are accustomed to being prompted for assurance
when, for instance, a directory or file is deleted at the operating system level.
<TT>Are you sure? (Y/N)</TT> is a common question asked before the operation is performed.
Using SQL, when you instruct the DBMS to delete a group of records from a table,
it obeys your command without asking. That is, when you tell SQL to delete a group
of records, it will really do it!</P>
<P>On Day 11 you will learn about transaction control. Transactions are database
operations that enable programmers to either <TT>COMMIT</TT> or <TT>ROLLBACK</TT>
changes to the database. These operations are very useful in online transaction-processing
applications in which you want to execute a batch of modifications to the database
in one logical execution. Data integrity problems will occur if operations are performed
while other users are modifying the data at the same time. For now, assume that no
transactions are being undertaken.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Some implementations, for example,
	Oracle, automatically issue a <TT>COMMIT</TT> command when you exit SQL. 
<HR>


</BLOCKQUOTE>

<P>Depending on the use of the <TT>DELETE</TT> statement's <TT>WHERE</TT> clause,
SQL can do the following:

<UL>
	<LI>Delete single rows
	<P>
	<LI>Delete multiple rows
	<P>
	<LI>Delete all rows
	<P>
	<LI>Delete no rows
</UL>

<P>Here are several points to remember when using the <TT>DELETE</TT> statement:

<UL>
	<LI>The <TT>DELETE</TT> statement cannot delete an individual field's values (use
	<TT>UPDATE</TT> instead). The <TT>DELETE</TT> statement deletes entire records from
	a single table.
	<P>
	<LI>Like <TT>INSERT</TT> and <TT>UPDATE</TT>, deleting records from one table can
	cause referential integrity problems within other tables. Keep this potential problem
	area in mind when modifying data within a database.
	<P>
	<LI>Using the <TT>DELETE</TT> statement deletes only records, not the table itself.
	Use the <TT>DROP TABLE</TT> statement (see Day 9) to remove an entire table.
</UL>

<H4><FONT COLOR="#000077">Example 8.7</FONT></H4>
<P>This example shows you how to delete all the records from <TT>COLLECTION</TT>
where <TT>WORTH</TT> is less than <TT>275</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>DELETE FROM COLLECTION</B>
  2  <B>WHERE WORTH &lt; 275;
</B>
4 rows deleted.
</FONT></PRE>
<P>The result is a table that looks like this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM COLLECTION;</B>

ITEM                     WORTH REMARKS
-------------------- --------- ------------------------------
NBA ALL STAR CARDS         300 SOME STILL IN BIKE SPOKES

STRING                    1000 SOME DAY IT WILL BE VALUABLE
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Like the <TT>UPDATE </TT>statement,
	if you omit a <TT>WHERE</TT> clause from the <TT>DELETE</TT> statement, all rows
	in that particular table will be deleted. 
<HR>


</BLOCKQUOTE>

<P>Example 8.8 uses all three data manipulation statements to perform a set of database
operations.
<H4><FONT COLOR="#000077">Example 8.8</FONT></H4>
<P>This example inserts some new rows into the <TT>COLLECTION</TT> table you used
earlier today.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>INSERT INTO COLLECTION</B>
  2  <B>VALUES('CHIA PET', 5,'WEDDING GIFT');</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">1 row created.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>INSERT INTO COLLECTION</B>
  2 <B> VALUES('TRS MODEL III', 50, 'FIRST COMPUTER');</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">1 row created.
</FONT></PRE>
<P>Now create a new table and copy this data to it:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> CREATE TABLE TEMP</B>
  2 <B> (NAME CHAR(20),</B>
  3  <B>VALUE NUMBER,</B>
  4  <B>REMARKS CHAR(40));</B>

Table created.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>INSERT INTO TEMP(NAME, VALUE, REMARKS)</B>
  2  <B>SELECT ITEM, WORTH, REMARKS</B>
  3 <B> FROM COLLECTION;</B>

4 rows created.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM TEMP;</B>

NAME                     VALUE REMARKS
-------------------- --------- ------------------------------
NBA ALL STAR CARDS         300 SOME STILL IN BIKE SPOKES
STRING                    1000 SOME DAY IT WILL BE VALUABLE
CHIA PET                     5 WEDDING GIFT
TRS MODEL III               50 FIRST COMPUTER
</FONT></PRE>
<P>Now change some values:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE TEMP</B>
  2  <B>SET VALUE = 100</B>
  3  <B>WHERE NAME = 'TRS MODEL III';</B>

1 row updated.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE TEMP</B>
  2  <B>SET VALUE = 8</B>
  3  <B>WHERE NAME = 'CHIA PET';</B>

1 row updated.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM TEMP;</B>

NAME                     VALUE REMARKS
-------------------- --------- ----------------------------
NBA ALL STAR CARDS         300 SOME STILL IN BIKE SPOKES
STRING                    1000 SOME DAY IT WILL BE VALUABLE
CHIA PET                     8 WEDDING GIFT
TRS MODEL III              100 FIRST COMPUTER
</FONT></PRE>
<P>And update these values back to the original table:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">INSERT COLLECTION
SELECT * FROM TEMP;
DROP TABLE TEMP;
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The <TT>DROP TABLE</TT> and <TT>CREATE TABLE</TT> statements are discussed in
greater detail on Day 9. For now, these statements basically do what their names
suggest. <TT>CREATE TABLE</TT> builds a new table with the format you give it, and
<TT>DROP TABLE</TT> deletes the table. Keep in mind that <TT>DROP TABLE</TT> permanently
removes a table, whereas <TT>DELETE FROM &lt;TableName&gt;</TT> removes only the
records from a table.</P>
<P>To check what you have done, select out the records from the <TT>COLLECTION</TT>
table. You will see that the changes you made now exist in the <TT>COLLECTION</TT>
table.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM COLLECTION;</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">NAME                     VALUE REMARKS
-------------------- -------- ----------------------------
NBA ALL STAR CARDS         300 SOME STILL IN BIKE SPOKES
STRING                    1000 SOME DAY IT WILL BE VALUABLE
CHIA PET                     8 WEDDING GIFT
TRS MODEL III              100 FIRST COMPUTER
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The previous example used all three data manipulation commands--<TT>INSERT</TT>,
<TT>UPDATE</TT>, and <TT>DELETE</TT>--to perform a set of operations on a table.
The <TT>DELETE</TT> statement is the easiest of the three to use.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Always keep in mind that any
	modifications can affect the referential integrity of your database. Think through
	all your database editing steps to make sure that you have updated all tables correctly.
	
<HR>