AppDividend
Latest Code Tutorials

SQL Delete Query Tutorial | Delete Statement In SQL Example

0

SQL Delete Query Tutorial | Delete Statement In SQL Example is today’s topic. The DELETE statement is used to remove existing records in a table. We have already seen the create table, insert row, update row, and now we will see how to remove or delete the row from the SQL table. You can use the WHERE clause with the DELETE query to delete the selected rows; otherwise, all the records would be eliminated.

SQL Delete Query

Delete query is a type of DML type of statement in which we manipulate the tables. If you are not careful while deleting the records from the table, then you will end up losing lots of data.

#Syntax

See the following syntax of SQL Delete Statement.

DELETE FROM table
WHERE [condition];

In the above query, we need to specify the table name and a condition; otherwise, the whole table will delete. You can also combine the N number of conditions using AND or OR operators.

See the following table.

 

SQL Delete Query Tutorial

Now, let’s write a query where we will remove the AFTERGAME SNEAKER row.

DELETE FROM Products
WHERE ProductName = 'AFTERGAME SNEAKER'

Okay, so if the query is executed successfully, then now select all the rows using the following query.

Select * from Products

Now, see the output. There are not four rows.

 

Delete Statement In SQL

If you want to remove all the records from the Products table, you do not have to use the WHERE clause, and the DELETE statement would be as follows.

DELETE from Products

It will remove all the data from the Products table.

#SQL Truncate Statement

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

#SQL DELETE related rows in multiple tables

It becomes more and more complicated when you want to delete the row in the table which is associated with the other rows in another table. We can define the relationships between the two tables using Foreign Key.

For instance, let’s say we have two tables.

  1. Categories
  2. Products

Each product belongs to one category, and a category has multiple products.

That means, if we delete a particular category, then we have to remove all the products related to that category; otherwise the integrity among the tables will be violated.

So, one table has a primary key, and another table has the foreign key, and foreign key defines the relationships between those tables.

See the following query.

DELETE from categories
WHERE id = 3;

DELETE from products
WHERE category_id = 3

Most database management systems (DBMS) allows us to create the foreign key constraint so that if you delete a row in the table, the corresponding rows to the related table are also removed automatically.

It ensures the integrity of the data. In our case, you just have to run the first DELETE statement only to delete the rows in two tables.

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

So, DELETE Statement permanently removes records from a table.

Finally, SQL Delete Query Tutorial | Delete Statement In SQL Example is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.