| Lesson 13 | The DELETE statement |
| Objective | Create a DELETE statement that removes one row from the database. |
SQL DELETE Statement
The UPDATE and DELETE statements share one common element: You must use a WHERE clause.
If you leave off the WHERE clause, you will end up changing values in the entire table. With the DELETE statement, there are even more drastic results.That is, you will end up deleting the entire contents of the table. Everyone does it once, and usually only once. From then on you will be so cautious about your UPDATE and DELETE statements that you will cringe when you submit your delete and update statements. Here's the general syntax for the DELETE statement:
From the customer table, if you want to delete the row that corresponds to Sheila Sasser, you can do so by indicating the unique customer ID in the WHERE clause:
The DELETE statement is one of the easiest and most deadly statements. We can't caution you strongly enough about using it.
By and large, you will not see this statement in day-to-day use of your SQL engine. Make sure you understand the effects of the DELETE statement before using it.
If you leave off the WHERE clause, you will end up changing values in the entire table. With the DELETE statement, there are even more drastic results.That is, you will end up deleting the entire contents of the table. Everyone does it once, and usually only once. From then on you will be so cautious about your UPDATE and DELETE statements that you will cringe when you submit your delete and update statements. Here's the general syntax for the DELETE statement:
DELETE FROM table WHERE...
From the customer table, if you want to delete the row that corresponds to Sheila Sasser, you can do so by indicating the unique customer ID in the WHERE clause:
DELETE FROM customer WHERE customerID=1
The DELETE statement is one of the easiest and most deadly statements. We can't caution you strongly enough about using it.
By and large, you will not see this statement in day-to-day use of your SQL engine. Make sure you understand the effects of the DELETE statement before using it.
DELETE Statement
The DELETE statement removes records from a table. The basic syntax is:
For example, the following statement removes all records from the Books table where the AuthorId is 7:
As is the case with UPDATE, the WHERE clause is very important in a DELETE statement. If you forget the WHERE clause, the DELETE statement removes every record from the table mercilessly and without remorse.
DELETE FROM table WHERE where_clause
For example, the following statement removes all records from the Books table where the AuthorId is 7:
DELETE FROM Books WHERE AuthorId = 7
As is the case with UPDATE, the WHERE clause is very important in a DELETE statement. If you forget the WHERE clause, the DELETE statement removes every record from the table mercilessly and without remorse.
MySQL Delete Statement
To delete data from a table, you use the MySQL DELETE statement. The following illustrates the syntax of the DELETE statement:DELETE FROM table_name WHERE condition;
If you want to delete contractors where the officeNumber is 5, you use the DELETE statement with the WHERE clause as shown in the following query:
DELETE FROM contractors WHERE officeCode = 5;
To delete all rows from the contractors table, use the DELETE statement without the WHERE clause as follows:
DELETE FROM contractors;
MySQL DELETE and LIMIT clause
If you want to limit the number of rows to delete, use the LIMIT clause as follows:DELETE FROM table_table LIMIT row_count;
Note that the order of rows in a table is unspecified, therefore, when you use the LIMIT clause, you should always use the ORDER BY clause.
DELETE FROM table_name ORDER BY x1, x2, ..., xn LIMIT row_count;
Ad SQL Database Programming
Delete Row - Exercise
Click the exercise link to practice using the DELETE statement.
Delete Row - Exercise
Delete Row - Exercise