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.
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 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
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.
0 Comments
CAN FEEDBACK
Emoji