<P>In this example you tried to insert another <TT>ITEM</TT> called <TT>STRING</TT>
into the <TT>COLLECTION</TT> table. Because this table was created with <TT>ITEM</TT>
as a unique value, it returned the appropriate error. ANSI SQL does not offer a solution
to this problem, but several commercial implementations include extensions that would
allow you to use something like the following:</P>
<PRE><FONT COLOR="#0066FF">IF NOT EXISTS (SELECT * FROM COLLECTION WHERE NAME = 'STRING'

INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING')
</FONT></PRE>
<P>This particular example is supported in the Sybase system.</P>
<P>A properly normalized table should have a unique, or key, field. This field is
useful for joining data between tables, and it often improves the speed of your queries
when using indexes. (See Day 10, &quot;Creating Views and Indexes.&quot;)


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Here's an <TT>INSERT</TT> statement
	that inserts a new employee into a table:</P>
	<PRE><FONT COLOR="#0066FF">SQL&gt; insert into employee_tbl values
     ('300500177', 'SMITHH', 'JOHN');

1 row inserted.</FONT></PRE>
	<P>After hitting Enter, you noticed that you misspelled <TT>SMITH</TT>. Not to fret!
	All you have to do is issue the <TT>ROLLBACK</TT> command, and the row will not be
	inserted. See Day 11, &quot;Controlling Transactions,&quot; for more on the <TT>ROLLBACK</TT>
	command. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077">The INSERT...SELECT Statement</FONT></H3>
<P>The <TT>INSERT...VALUES</TT> statement is useful when adding single records to
a database table, but it obviously has limitations. Would you like to use it to add
25,000 records to a table? In situations like this, the <TT>INSERT...SELECT</TT>
statement is much more beneficial. It enables the programmer to copy information
from a table or group of tables into another table. You will want to use this statement
in several situations. Lookup tables are often created for performance gains. Lookup
tables can contain data that is spread out across multiple tables in multiple databases.
Because multiple-table joins are slower to process than simple queries, it is much
quicker to execute a <TT>SELECT</TT> query against a lookup table than to execute
a long, complicated joined query. Lookup tables are often stored on the client machines
in client/server environments to reduce network traffic.</P>
<P>Many database systems also support temporary tables. (See Day 14, &quot;Dynamic
Uses of SQL.&quot;) Temporary tables exist for the life of your database connection
and are deleted when your connection is terminated. The <TT>INSERT...SELECT</TT>
statement can take the output of a <TT>SELECT</TT> statement and insert these values
into a temporary table.</P>
<P>Here is an example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> insert into tmp_tbl
</B>  2  <B>select * from table;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">19,999 rows inserted.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You are selecting all the rows that are in <TT>table</TT> and inserting them into
<TT>tmp_tbl</TT>.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Not all database management systems
	support temporary tables. Check the documentation for the specific system you are
	using to determine if this feature is supported. Also, see Day 14 for a more detailed
	treatment of this topic. 
<HR>


</BLOCKQUOTE>

<P>The syntax of the <TT>INSERT...SELECT</TT> statement is as follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">INSERT INTO table_name
(col1, col2...)
SELECT col1, col2...
FROM tablename
WHERE search_condition
</FONT></PRE>
<P>Essentially, the output of a standard <TT>SELECT</TT> query is then input into
a database table. The same rules that applied to the <TT>INSERT...VALUES</TT> statement
apply to the <TT>INSERT...SELECT</TT> statement. To copy the contents of the <TT>COLLECTION</TT>
table into a new table called <TT>INVENTORY</TT>, execute the set of statements in
Example 8.3.
<H4><FONT COLOR="#000077">Example 8.3</FONT></H4>
<P>This example creates the new table <TT>INVENTORY</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE TABLE INVENTORY</B>
  2  <B>(ITEM CHAR(20),</B>
  3 <B> COST NUMBER,</B>
  4  <B>ROOM CHAR(20),</B>
  5  <B>REMARKS CHAR(40));</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">Table created.
</FONT></PRE>
<P>The following <TT>INSERT</TT> fills the new <TT>INVENTORY</TT> table with data
from <TT>COLLECTION</TT>.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>INSERT INTO INVENTORY (ITEM, COST, REMARKS)</B>
  2  <B>SELECT ITEM, WORTH, REMARKS</B>
  3 <B> FROM COLLECTION;</B>

6 rows created.
</FONT></PRE>
<P>You can verify that the <TT>INSERT</TT> works with this <TT>SELECT</TT> statement:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM INVENTORY;</B>

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

6 rows selected.
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The data appears to be in the table;
	however, the transaction is not finalized until a <TT>COMMIT</TT> is issued. The
	transaction can be committed either by issuing the <TT>COMMIT</TT> command or by
	simply exiting. See Day 11 for more on the <TT>COMMIT</TT> command. 
<HR>


</BLOCKQUOTE>

<H5>ANALYSIS:</H5>
<P>You have successfully, and somewhat painlessly, moved the data from the <TT>COLLECTION</TT>
table to the new <TT>INVENTORY</TT> table!</P>
<P>The <TT>INSERT...SELECT</TT> statement requires you to follow several new rules:

<UL>
	<LI>The <TT>SELECT</TT> statement cannot select rows from the table that is being
	inserted into.
	<P>
	<LI>The number of columns in the <TT>INSERT INTO</TT> statement must equal the number
	of columns returned from the <TT>SELECT</TT> statement.
	<P>
	<LI>The data types of the columns in the <TT>INSERT INTO</TT> statement must be the
	same as the data types of the columns returned from the <TT>SELECT</TT> statement.
</UL>

<P>Another use of the <TT>INSERT...SELECT</TT> statement is to back up a table that
you are going to drop, truncate for repopulation, or rebuild. The process requires
you to create a temporary table and insert data that is contained in your original
table into the temporary table by selecting everything from the original table. For
example:</P>
<PRE><FONT COLOR="#0066FF">   SQL&gt; <B>insert into copy_table</B>
     2  <B>select * from original_table;</B>
</FONT></PRE>
<P>Now you can make changes to the original table with a clear conscience.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Later today you learn how to input
	data into a table using data from another database format. Nearly all businesses
	use a variety of database formats to store data for their organizations. The applications
	programmer is often expected to convert these formats, and you will learn some common
	methods for doing just that. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">The UPDATE Statement</FONT></H2>
<P>The purpose of the <TT>UPDATE</TT> statement is to change the values of existing
records. The syntax is</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">UPDATE table_name
SET columnname1 = value1
[, columname2 = value2]...
WHERE search_condition
</FONT></PRE>
<P>This statement checks the <TT>WHERE</TT> clause first. For all records in the
given table in which the <TT>WHERE</TT> clause evaluates to <TT>TRUE</TT>, the corresponding
value is updated.
<H4><FONT COLOR="#000077">Example 8.4</FONT></H4>
<P>This example illustrates the use of the <TT>UPDATE</TT> statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> UPDATE COLLECTION</B>
  2  <B>SET WORTH = 900</B>
  3  <B>WHERE ITEM = 'STRING';</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">1 row updated.
</FONT></PRE>
<P>To confirm the change, the query</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM COLLECTION</B>
  2  <B>WHERE ITEM = 'STRING';</B>
</FONT></PRE>
<P>yields</P>
<PRE><FONT COLOR="#0066FF">ITEM                     WORTH REMARKS
-------------------- --------- ------------------------------

STRING                     900 SOME DAY IT WILL BE VALUABLE
</FONT></PRE>
<P>Here is a multiple-column update:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>update collection</B>
  2  <B>set worth = 900, item = ball</B>
  3  <B>where item = 'STRING';</B>

1 row updated.
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Your implementation might use a
	different syntax for multiple-row updates. 
<HR>
</P>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice in the set that <TT>900</TT>
	does not have quotes, because it is a numeric data type. On the other hand, <TT>String</TT>
	is a character data type, which requires the quotes. 
<HR>


</BLOCKQUOTE>

<H4><FONT COLOR="#000077">Example 8.5</FONT></H4>
<P>If the <TT>WHERE</TT> clause is omitted, every record in the <TT>COLLECTION</TT>
table is updated with the value given.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE COLLECTION
</B>  2 <B> SET WORTH = 555;</B>

6 rows updated.
</FONT></PRE>
<P>Performing a <TT>SELECT</TT> query shows that every record in the database was
updated with that value:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM COLLECTION;</B>

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

6 rows selected.
</FONT></PRE>
<P>You, of course, should check whether the column you are updating allows unique
values only.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>If you omit the <TT>WHERE</TT>
	clause from the <TT>UPDATE</TT> statement, all records in the given table are updated.
	
<HR>