how to delete column in sql

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

The DELETE statement removes one or more rows in a table permanently. The syntax of the DELETE statement is as follows:

DELETE FROM table WHERE condition;

To remove one or more rows in a table:

First, you specify the table name where you want to remove data in the DELETE FROM clause.
Second, you put a condition in the WHERE clause to specify which rows to remove. If you omit the WHERE clause, the statement will remove all rows in the table.

If you want to remove all rows in a big table, you should use the TRUNCATE TABLE statement which is more efficient than the DELETE statement.

SQL DELETE examples

Let's take a look at some examples of using the DELETE statement.

SQL DELETE – deleting one row in a table

The following statement deletes the employee with id 3 from the employees table:

DELETE FROM employees WHERE employeeID = 3;

SQL DELETE – deleting all rows in a table

To remove all rows in the employees table, you just execute the following query: (not recommended and make a backup before you do this)

DELETE FROM employees;

SQL DELETE – deleting related rows in multiple tables

It becomes more complicated when you want to delete a row in a table that is associated with other rows in another table.

For example, each employee is working in one or more territories and each territory has multiple employees. The employeeterritories table is used to store relationships between employees and territories.

SQL DELETE statement - employees and territories tables

When you remove a row in the employees table, you must also remove the related rows in the employeeterritories table. In order to do so, you have to execute two DELETE statements as follows:

DELETE FROM employees WHERE employeeID = 3;

DELETE FROM employeeterritories WHERE employeeID = 3

Most database management systems allow you to create a foreign key constraint so that if you delete a row in a table, the corresponding rows the related table are also removed automatically. This ensures the integrity of the data. In this case, you just have to execute the first DELETE statement only to delete rows in two tables.

In case the database management system does not support the foreign key constraint, you have to execute both DELETE statements in a single transaction to make sure that the statements execute in all-or-nothing mode.

In this tutorial, we have shown you how to use SQL DELETE statement to delete one or more rows in a table.