Inserting, Updating,and Deleting Data - Java Database Programming Bible - page 196

UPDATE Contact_Info

SET Street = '55 Broadway', ZIP = '10006'

WHERE First_Name = 'Michael' AND Last_Name = 'Corleone';

This statement first evaluates the WHERE clause to find all records with matching First_Name and
Last_Name. It then makes the address change to all of those records.

Caution

If you omit the WHERE clause from the UPDATE statement, all records in the given
table are updated.

Using Calculated Values with UPDATE

You can also use the UPDATE statement to update columns with calculated values. For example, if you
add stock to your inventory, instead of setting the Qty column to an absolute value, you can simply add
the appropriate number of units with a calculated UPDATE statement like this:

UPDATE Inventory

SET Qty = QTY + 24

WHERE Name = 'Corn Flakes';

When you use a calculated UPDATE statement like this, you need to make sure that you observe the
rules for INSERTS and UPDATES mentioned earlier. In particular, ensure that the data type of the
calculated value is the same as the data type of the field you are modifying, as well as short enough to
fit in the field.

Common Problems with UPDATE

Two common problems can result from the use of calculated values:

Truncation can result from number conversions, such as conversion from a real number to an
integer.

Overflow occurs when the resulting value is larger than the capacity of the column. This causes
the database system to return an error.

Problems of this type can be avoided if you observe the rules for INSERTS and UPDATES mentioned
earlier.

Listing 6-3: Using UPDATE with JDBC

package jdbc_bible.part2;

import java.awt.event.*;

import java.sql.*;

import sun.jdbc.odbc.JdbcOdbcDriver;