UPDATE Syntax

• Changes existing rows in a table.
• Can change a single row, several rows, or all the rows in a table.
• Different databases implement different advanced features.
• All implement this basic syntax:

  UPDATE table SET column_name = expression [WHERE search_conditions]

• Update more than one column by adding more column_name = expression assignments.
• Delimit them with commas.

The WHERE Clause

• Optional
• Specifies the conditions under which you want values to be updated.
• Without a WHERE clause, every row in the table is updated.

Examples

• Discount all titles 20% if their price is greater than $100

  UPDATE title SET price = price * 0.8 WHERE price > 100

• Change all books whose edition is "1th" to "1st" and increase the price 5%

  UPDATE title SET edition = "1st", price = price * 1.05 WHERE edition = "1th"

What Happened

• To find out how many rows were actually updated, use the rows method:

  $rows_updated = $sth->rows;

• Zero rows indicates some kind of error.

Example

$sth = $dbh->prepare( qq{ UPDATE title SET price = price * 0.8 WHERE price > 100 } );
$sth->execute or die "Failed to execute: $DBI::errstr\n";
$rows_updated = $sth->rows;
print "I just updated $rows_updated rows\n";

Review

Which columns in a table can be modified by the UPDATE statement?