Updating and Deleting MySQL Data

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

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 thoise columns. Finally, it need 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.

UPDATE products
SET prod_desc = 'Size 10 Red Shoe'
WHERE prod_id = 12134;

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:

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