Java Reference
In-Depth Information
For instance, the following update statement would modify the “Joe Programmer” row by adding address
information and changing the payrate column:
UPDATE tntdb.employee
SET street = '2 Maple Ave.',
city = 'Enid',
state = 'OK',
zip = '77777',
payrate = 19
WHERE empnum = '222'
If we ran the following statement to return all rows and columns of the employee table:
SELECT * FROM tntdb.employee
the returned data would be:
111 Mary Worker 1 Main St. Enid OK 77777 17.5 3
222 Joe Programmer 2 Maple Ave. Enid OK 77777 19.0 2
In this example, only one row was modified, however, a
where
condition can identify many rows. For instance,
if the zip code for Enid changed to 77778, we could update all the Enid student records with the following single
update
statement:
UPDATE tntdb.employee
SET zip = '77778'
WHERE city = 'Enid'
Selecting all records and rows would return the following data:
111 Mary Worker 1 Main St. Enid OK 77778 17.5 3
222 Joe Programmer 2 Maple Ave. Enid OK 77778 19.0 2
Beware: if a
where
condition is not specified in an
update
statement, all rows will be changed. This is also true
for all statements that support
where
conditions.
Of course, the ultimate modification to a row is to delete it. Deletions are performed by using the
delete from
keywords followed by the schema and table names and, optionally:
The
where
keyword, and
A condition that identifies the rows to be deleted.
The following statement would delete all rows from the table:
DELETE FROM tntdb.employee
but the following would only delete the Mary Worker row:
DELETE FROM tntdb.employee WHERE empName = 'Mary Worker'