Java Reference
In-Depth Information
These rules are obvious, but breaking them accounts for a lot of SQL exceptions,
particularly when you save data in the wrong field order. Another common error is to
try and insert the wrong number of data fields.
Using INSERT ... SELECT
Another common use of the INSERT statement is to copy subsets of data from one
table to another. In this case, the INSERT statement is combined with a SELECT
statement, which queries the source table for the desired records. The advantage of
this approach is that the whole process is carried out within the RDBMS, avoiding the
overhead of retrieving records and reinserting them externally.
An example of a situation where you might use INSERT...SELECT is the creation of a
table containing only the first and last names from the Customers Table. To insert the
names from the original Customers Table, use a SQL INSERT...SELECT command
to select the desired fields and insert them into the new Names Table. Here's an
example:
INSERT INTO Names
SELECT First_Name, Last_Name FROM Customers;
Essentially, This command tells the database management system to perform two
separate operations internally:
1. A SELECT to query the Customers Table for the FName and LName fields from all records
2. An INSERT to input the resulting record set into the new Names Table
By performing these operations within the RDBMS, the use of the INSERT...SELECT
command eliminates the overhead of retrieving the records and reinserting them.
Using the WHERE clause with INSERT ... SELECT
The optional WHERE clause allows you to make conditional queries. For example,
you can get all records in which the last name is "Corleone" and insert them into the
Names Table with the following statement:
INSERT INTO Names
SELECT First_Name, Last_Name FROM Customers WHERE Last_Name =
'Corleone';
The UPDATE Statement
The UPDATE command is used to modify the contents of individual columns within a
set of rows. The UPDATE command is normally used with a WHERE clause, which is
used to select the rows to be updated.
