The UPDATE Statement
The UPDATE statement can be used to update specific rows in a table. In other words, this statement modifies zero or more rows in the table:
UPDATE
table_name
SET
column_name_value_pairs
WHERE
row_filter
;
where column_name_value_pairs in the SET clause is a comma-delimited list of pairs of column names and values:
column_name
=
column_value
, …
and row_filter in the WHERE clause specifies a list of conditions to indicate which specific rows should be updated.
All rows that meet the row_filter criteria in table_name will have their columns specified in the column_name_value_pairs set to the corresponding values in column_name_value_pairs.
The following statement updates a duration value for an existing row in the compositions table:
UPDATE compositions SET duration = 240 WHERE isrc LIKE ‘%91736697’
OR duration > 231;
The above UPDATE statement will update the first row (isrc ‘91736697’) in Table 24.2 by setting the duration to 240.
The WHERE clause is optional. When omitted, all rows will have their columns specified in the column_name_value_pairs set to the corresponding values in column_ name_value_pairs.
UPDATE compositions SET duration = 250;
The above UPDATE statement will update the duration column for all rows to 250 in Table 24.2.
The DELETE Statement
The DELETE statement can be used to delete specific rows in a table that meet the criteria in the WHERE clause. This statement deletes zero or more rows from the specified table:
DELETE FROM
table_name
WHERE
row_filter
;
where row_filter in the WHERE clause is the criteria for which rows should be deleted from table_name in the FROM clause.
The following statement deletes a row from the compositions table:
DELETE FROM compositions WHERE isrc = ‘ushm91736697’;
The row with isrc having the value ‘ushm91736697’ in the compositions table will be deleted from Table 24.2.
The WHERE clause is optional. Care must be taken, as omitting it will delete all rows from the table.
DELETE FROM compositions;
There are many other statements available in SQL described by the ANSI SQL standard and additional statements which may be implemented by database providers.
This book does not have a goal of covering SQL in detail, but provides a brief introduction to ensure a minimum level of understanding of how relational databases operate and thereby an understanding of how Java programs interact with relational databases.
Java certification focuses on Java, not on SQL. However, it is essential to learn database concepts and understand SQL if one wants to work with relational databases.
