In Microsoft SQL Server (MSSQL), the DELETE statement is used to remove one or more rows from a table based on specified conditions. It's commonly used to remove unwanted or outdated data from a database.
Let's go through the examples:- Example 1: Deleting Specific Rows Suppose we have a table named employees with the following data:- | id | name | age | city | |----|----------|-----|----------| | 1 | John | 30 | New York | | 2 | Emily | 28 | London | | 3 | Michael | 35 | Paris | | 4 | Sophia | 32 | Tokyo | Step 1:- Write the DELETE statement to remove rows where the city is Tokyo. DELETE FROM employees WHERE city = 'Tokyo'; Step 2: Execute the DELETE statement. Step 3: Show the updated table. | id | name | age | city | |----|----------|-----|----------| | 1 | John | 30 | New York | | 2 | Emily | 28 | London | | 3 | Michael | 35 | Paris | Output: The row with ID 4 (Sophia) has been deleted because the city was Tokyo.
Example 2: Deleting All Rows
Step 1: Write the DELETE statement without a WHERE clause to delete all rows from the employees table.
DELETE FROM employees;
Step 2: Execute the DELETE statement.
Step 3: Show the updated table (empty table).
| id | name | age | city |
Output: All rows from the employees table have been deleted. Example 3: Deleting with a Subquery Suppose we have another table named terminated_employees containing IDs of employees who have been terminated: | id | |----| | 2 | | 4 | Step 1: Write a DELETE statement using a subquery to delete rows from the employees table where the ID matches those in the `terminated_employees` table. DELETE FROM employees WHERE id IN (SELECT id FROM terminated_employees); Step 2: Execute the DELETE statement. Step 3: Show the updated table after deletion. | id | name | age | city | |----|----------|-----|----------| | 1 | John | 30 | New York | | 3 | Michael | 35 | Paris |
Output: Rows with IDs 2 and 4 (Emily and Sophia) have been deleted from the employees table.
Here are five frequently asked questions (FAQs) about the DELETE statement in Microsoft SQL Server (MSSQL):- 1. What is the purpose of the DELETE statement in MSSQL?
- The DELETE statement is used to remove one or more rows from a table in a SQL Server database. It allows users to selectively remove data based on specified conditions. 2. Can I delete all rows from a table using the DELETE statement?
- Yes, you can delete all rows from a table by using the DELETE statement without specifying a WHERE clause. However, exercise caution as this action will remove all data from the table. 3. How do I delete specific rows from a table in MSSQL?
- To delete specific rows, you can use the DELETE statement with a WHERE clause specifying the conditions that must be met for a row to be deleted. For example: DELETE FROM table_name WHERE condition; 4. Is it possible to delete data from multiple tables simultaneously in MSSQL?
- Yes, you can delete data from multiple tables using join operations in the DELETE statement. This is achieved by specifying multiple tables in the FROM clause and using appropriate join conditions. 5. Can I roll back a DELETE operation in MSSQL?
- Yes, if the database is in a transactional mode (e.g., using the BEGIN TRANSACTION and COMMIT TRANSACTION statements), you can roll back a DELETE operation by issuing a ROLLBACK statement before committing the transaction. This will revert the database to its state before the DELETE operation was executed.
