Deleting Data in MySQL

By: Sathya Narayana in MySQL Tutorials on 2010-10-24

To delete a given row or set of rows, you use the DELETE FROM...WHERE command. For instance, you can delete the book called Mr Bunny's Guide to JDO with this command:

mysql> DELETE FROM book
          -> WHERE title = "Mr Bunny's Guide to JDO";

Now execute a SELECT command to retrieve all books from the table, and you'll see that the record for that book no longer appears:

+----+-------------------------------------------+-------+
| id | title                                     | price |
+----+-------------------------------------------+-------+
| 1 | Lord of the Things                         | 8.99  |
| 3 | Parachuting for You and Your Kangaroo      | 19.99 |
+----+-------------------------------------------+-------+

Note

Be careful when using DELETE because once a row is removed, it's lost forever. Hence, be particularly wary when specifying a nonunique column in the WHERE clause, such as you have here. In your small database, you know that only a single row will be deleted, but in a real-world database, you can easily end up accidentally deleting a whole bunch of records if you use a badly thought-out WHERE clause. You can reduce the chances of this happening by specifying a WHERE clause on a uniquely valued field (such as a primary key).

We've now covered enough of the basics of the SQL syntax to move on and show how you can use it to access databases in a JSP page.

Previous
<< Modifying data and using WHERE clause in MySQL

Next
ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client >>