Java Reference
In-Depth Information
A
SELECT
(to query the Contact_Info Table for the FName and LName fields from all
records)
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.
The WHERE clause
The optional
WHERE
clause allows you to make conditional queries; for example, you can get all
records where the last name is "Corleone" and insert them into the Names Table with this statement:
INSERT INTO Names
SELECT First_Name, Last_Name FROM Contact_Info WHERE Last_Name =
'Corleone';
Using INSERT ... SELECT with JDBC
As with any other SQL command, it is easy to use
INSERT ... SELECT
with JDBC. If you substitute
the code snippet of
Listing 6-2
for the
main()
of
Listing 6-1
and run the example again, you will create
a Name Table populated with the first and last names.
Listing 6-2: Using INSERT ... SELECT with JDBC
public static void main(String args[]){
DataInserter inserter = new DataInserter();
String SQLCommand = "INSERT INTO NAMES "+
"SELECT First_Name,Last_Name FROM CONTACT_INFO "+
"WHERE Last_Name = 'Corleone'; ";
inserter.execute(SQLCommand);
}
}
Once you have data in a table, you are likely to have to update it to reflect changes in data fields like
addresses or inventory item count. The
next section
shows how to use the SQL
UPDATE
command to
modify data in a table.
The UPDATE Statement
A frequent requirement in database applications is updating records. For example, when a contact
moves, you need to change his or her address. Do this with the
SQL UPDATE
statement, using a
WHERE
clause to identify the record you want to change. Here's an example:
