Deleting Data in MySQL

By: Sathya Narayana 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 |
+----+-------------------------------------------+-------+

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.

Most Viewed Articles (in MySQL )

MySQL Strengths and Weaknesses

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

Modifying data and using WHERE clause in MySQL

Table __________ is marked as crashed and should be repaired.

Changing the Structure of an Existing Table in MySQL

Querying the Database in MySQL

What is SQL Injection

Inserting Data into Tables in MySQL

Deleting Data in MySQL

Windows cannot access the specified device, path or file. You may not have the appropriate permissions to access them.