Deleting Data in MySQL

By: Sathya Narayana Viewed: 153226 times  Printer Friendly Format    

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 |


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.

Most Viewed Articles (in MySQL )

Latest Articles (in MySQL)

Comment on this tutorial