SQL DELETE

 

SQL DELETE


Summary: in this tutorial, you will learn how to use the SQL DELETE statement to delete one or more rows in a table.

Introduction to SQL DELETE statement

To remove one or more rows from a table, you use the DELETE statement. The general syntax for the DELETE statement is as follows:

DELETE FROM table_name WHERE condition;

First, provide the name of the table where you want to remove rows.

Second, specify the condition in the WHERE clause to identify the rows that need to be deleted. If you omit the WHERE clause all rows in the table will be deleted. Therefore, you should always use the DELETE statement with caution.

Generally speaking, the DELETE the statement does not return a result set as the SELECT statement. However, it does return the number of rows deleted.

SQL DELETE statement examples

We will use the employees and dependents tables to demonstrate the DELETE statement.

employees_dependents_tables

SQL DELETE one row in a table

Suppose David, who has employee id 105, wants to remove Fred from his dependent list. We know that Fred has the dependent id 16, so we use the following DELETE statement to remove Fred from the dependents table.

DELETE FROM dependents WHERE dependent_id = 16;

Because the WHERE the clause contains the primary key expression that identifies Fred, the DELETE statement removes just one row.

You can verify that the row with the dependent id 16 has been deleted by using the following statement:

SELECT COUNT(*) FROM dependents WHERE dependent_id = 16;
SQL DELETE one row example

SQL DELETE multiple rows example

To delete multiple rows in a table, you use the condition in the WHERE clause to identify the rows that should be deleted. For example, the following statement uses the IN operator to include the dependents of the employees with the id is 100, 101, or 102.

DELETE FROM dependents WHERE employee_id IN (100 , 101, 102);

SQL DELETE rows from related tables

One employee may have zero or many dependents while one dependent belongs to only one employee. The employee_id column in the dependents table links to the employee_id column in the employees table.

The relationship between the employees and dependents tables are one-to-many.

Logically, a dependent cannot exist without referring to an employee. In other words, when you delete an employee, his or her dependents must be deleted as well.

For example, to remove the employee id 192 and all  the employee’s dependents, you need to execute two DELETE statements as follows:

DELETE FROM employees WHERE employee_id = 192; DELETE FROM dependents WHERE employee_id = 192;

Most database systems support the foreign key constraint so that when one row from a table is deleted, the rows in the foreign key tables are also removed automatically.

Therefore, when the following DELETE the statement is executed:

DELETE FROM employees WHERE employee_id = 192;

All the rows with employee_id 192 are also removed automatically.

To remove all rows from a table more efficiently, you use the TRUNCATE TABLE statement instead of using the DELETE statement without a WHERE clause.

Now you should understand the SQL DELETE statement and how to apply it to remove one or more rows in a table.

Reactions

Post a Comment

0 Comments