Delete Row in SQL

The DELETE query in SQL is used to remove  or delete record or row from a table. It allows you to delete multiple rows using Where condition that match specific conditions. you can use multiple condition to delete row. you can delete multiple rows at a time .It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact.


delete statement in sql


some important points about Delete statement:

  • The DELETE statement is used to remove specific rows from a table based on specified conditions.
  • It is a data manipulation statement that selectively deletes rows that match the given criteria.
  • DELETE retains the table structure, indexes, and other associated objects, and only removes the specified rows.
  •  Cascading deletions in a database automatically delete related records in child tables when a record in the parent table is deleted. It helps maintain data consistency and referential integrity. However, caution should be exercised when using cascading deletions to avoid unintended data loss.
To know more visit:
Select statement 

About Delete statement

In DELETE statement DELETE is a keyword used to indicate that you want to remove data from a table.

FROM specifies the table from which you want to delete rows.

WHERE is an optional clause that allows you to specify conditions for deleting rows.

The conditions in the WHERE clause determine which rows will be deleted. For example, you can delete rows where a certain column has a specific value.

 Here's the basic syntax of the DELETE statement:


Example

        
          DELETE FROM table_name
          WHERE condition;
Let's break down the statement:

DELETE FROM: This phrase indicates that you want to delete records from a specific table.

table_name: Specifies the name of the table from which you want to delete records.

WHERE: This optional keyword is used to specify the condition that must be satisfied for a record to be deleted. If you omit the WHERE clause, all records in the table will be deleted.

condition: The condition is a logical expression that determines which records should be deleted based on specific criteria. For example, you might specify column_name = value to delete records where a particular column equals a certain value.

Here's an example of a DELETE statement that deletes a specific record from a  "employees" table based on an employee's ID:


Example

        
          DELETE FROM employees
          WHERE employee_id = 1001;
This statement would delete the record(s) from the "employees" table where the "employee_id" column has a value of 1001.

Delete multiple rows

To delete multiple rows in SQL, you can use the DELETE statement with the WHERE clause to specify the conditions for the rows you want to delete. This allows you to remove multiple rows at once based on specific criteria.

Here's a simple example that demonstrates deleting multiple rows from a table called "customers" based on specific conditions:

Example

        
          DELETE FROM customers
          WHERE country = 'USA' AND status = 'Inactive';
In this example, the query will delete all rows from the "customers" table where the country is 'USA' and the status is 'Inactive'.

Delete rows with Joins


You can perform a DELETE operation using JOINs to delete rows that meet certain conditions across multiple tables. For example:

Example

        
          DELETE t1
          FROM table1 t1
          JOIN table2 t2 ON t1.column_name = t2.column_name
          WHERE condition;


This allows you to delete rows from the specified table (t1) by joining it with another table (t2) based on a condition.

Delete All Rows

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact.
Here is the example:



Example

        
          DELETE FROM table_name;

Difference Between Delete, Drop and Truncate Statement

DELETE:


  • What it Does: 

DELETE is like erasing specific rows from a table using conditions you specify. It's like removing certain items from a collection based on criteria.

  • Control:

 You have control over which rows to delete by using conditions in the WHERE clause. It's selective and precise.

  • Rollback:

 You can undo the changes using a transaction rollback if needed, meaning you can bring back the deleted data.

  • Table Structure:
 DELETE only removes rows, keeping the table structure and other related objects like indexes intact.
  • Performance: 
Slower, as it logs each row deletion and can trigger related operations like triggers.

DROP:


  • What it Does:

 DROP is more drastic – it removes the entire table and all its data, like throwing away the entire collection.
  • Control: 
You can't be selective – the entire table and its contents vanish instantly.
  • Rollback: 
No undo here. Once you DROP a table, it's gone for good.
  • Table Structure:
 DROP deletes the entire table along with its structure and related objects. It's like erasing the collection and its storage.
  • Performance:
 Fastest because it's an all-or-nothing action. No logging of individual rows.

TRUNCATE:

  • What it Does:

 TRUNCATE is like a quick reset – it removes all rows from a table, leaving the table structure intact.
  • Control: 
No conditions here. All rows go away in one go, but you keep the table ready for new data.
  • Rollback:
 Like DROP, you can't undo it. Once you TRUNCATE, data is gone.
  • Table Structure:
 TRUNCATE keeps the table structure, indexes, and related objects, ready for new data to come in.
  • Performance:
 Faster than DELETE as it deallocates data pages, but no individual row logging.


difference between delete drop and truncate

Conclusion:

The DELETE statement in SQL is used to remove specific rows from a table based on specified conditions. It is a data manipulation statement that permanently removes the selected rows from the table. It is important to exercise caution when using the DELETE statement to avoid unintended data loss. Always double-check the conditions in the WHERE clause to ensure that you are deleting the intended rows. Additionally, consider taking backups of your data before executing any DELETE queries.