View source for Updating and Deleting MySQL Data

<table border="0" cellspacing="0" width="100%"><tr>
<td width="20%">[[Inserting Data into a MySQL Database|Previous]]<td align="center">[[MySQL Essentials|Table of Contents]]<td width="20%" align="right">[[Retrieving Data From a MySQL Database|Next]]</td>
<tr>
<td width="20%">Inserting Data into a MySQL Database<td align="center"><td width="20%" align="right">Retrieving Data From a MySQL Database</td>
</table>
<hr>


<htmlet>mysql</htmlet>


Once data has been added to a MySQL database table, it is invariably necessary to delete or update some or all of that data. In this chapter we will cover the issue of updating and deleting data in table rows.

== Updating Database Data ==
<htmlet>adsdaqbox_flow</htmlet>
When updating data in a table it is possible to either update specific rows, or to update all the rows in a table. These tasks are achieving using the SQL ''UPDATE'' statement combined with the ''SET'' and ''WHERE'' keywords.

The ''UPDATE'' statement requires a few items of information in order to operate. Firstly, it needs the name of the table that is to be updated. Secondly, it needs to know which columns are to be updated, and the new values for those columns. Finally, it needs information about which specific rows are to be updated (controlled by the ''WHERE'' keyword). The ''WHERE'' keyword is probably the most important part of the statement to remember. Without a ''WHERE'' condition, the update will be applied to every row in the table.

As with most things in life, this is best demonstrated through an example. The following SQL statement is designed to change the product description column of a row in a table where the prod_id column is equal to 12134.

<pre>
UPDATE products
SET prod_desc = 'Size 10 Red Shoe'
WHERE prod_id = 12134;
</pre>

In the above example, the ''SET'' section of the statement identifies the column to be updated and the value to which it is to be changed. The ''WHERE'' section of the statement identifies the row, or rows to be updated.

It is also possible to update multiple columns with a single ''UPDATE'' statement. This requires the use of multiple ''column = value'' expressions in ''UPDATE'' statement. For example, we can easily extend our previous example to update the prod_name and prod_desc columns of our products database table:

<pre>
UPDATE products
SET prod_desc = 'Size 10 Red Shoe',
    prod_name = 'Big Red Shoes'
WHERE prod_id = 12134;
</pre>

== Ignoring Update Errors ==

If an UPDATE statement is designed to update multiple rows, and an error is encountered attempting to update some of those rows, the entire update is canceled and any rows that had been changed are reverted to their original values. Use of the ''IGNORE'' keyword will cause the update to continue, simply skipping any rows which presented a problem:

<pre>
UPDATE IGNORE products
SET prod_desc = 'Size 10 Red Shoe',
    prod_name = 'Big Red Shoes'
WHERE prod_id = 12134;
</pre>

== Deleting Database Data ==

Either all rows, or only specific rows in a table can be deleted using the SQL ''DELETE'' statement. Once again, this statement can, and indeed should, be used in conjunction with the ''WHERE'' clause. Omission of the ''WHERE'' keyword will result in all rows in a table being deleted. In fact, it is often even recommended that the criteria to be used for the ''WHERE'' filter first be tested with a ''SELECT'' statement to ensure it does exactly what you intended.

The following SQL statement removes a row from our products table where the prod_id field is equal 12134:

<pre>
DELETE FROM products WHERE prod_id = 12134;
</pre>

Keep in mind when using the ''DELETE'' statement to remove all rows in a table that only the rows are removed, and that the table itself remains. To delete the table and its contents use the ''DROP TABLE'' SQL statement:

<pre>
DROP TABLE products;
</pre>

Once again, use this statement is caution. Once a table has been deleted it is gone for good (unless you have performed a recent backup).

== Summary ==

In this chapter of [[MySQL Essentials]] we explored how to update and delete rows in a table. We have also learned about the importance of using ''WHERE'' keyword to control which rows get updated or deleted when the statement is executed.

<htmlet>mysql</htmlet>

<hr>
<table border="0" cellspacing="0" width="100%"><tr>
<td width="20%">[[Inserting Data into a MySQL Database|Previous]]<td align="center">[[MySQL Essentials|Table of Contents]]<td width="20%" align="right">[[Retrieving Data From a MySQL Database|Next]]</td>
<tr>
<td width="20%">Inserting Data into a MySQL Database<td align="center"><td width="20%" align="right">Retrieving Data From a MySQL Database</td>
</table>