SQL Delete statement is used to delete records from table based on specific conditions which are specified in where clause.
- It is a DML command.
- It deletes the records based on conditions specified in the WHERE clause , and removes all the records if there is no Where clause. Condition given in where clause can be any logical, comparison operator asย >= ,<=,= , <> , > ,< , and like and so on..
- Identity column in a table is not reset when records are deleted from a table.
- It removes records one at a time and keeps an entry in the transaction log for each deleted row.
- It supports the Triggers.
- Delete statement can be rolled back.
SYNTAX
DELETE FROM table_name WHERE CONDITION..
Lets look at an example of Delete statement in SQLย SERVER.
First we Create a table named as SALES and insert some records in table.
CREATE TABLE dbo.SALES (SALES_ID INT IDENTITY(1,1)ย , ITEM VARCHAR(50), ITEM_CODE VARCHAR(15), ITEM_QTY INT, PURCHASED_DATE DATE ) INSERT INTO dbo.SALES (ITEM, ITEM_CODE, ITEM_QTY, PURCHASED_DATE) VALUES( 'LAPTOP- HP','PAVILLION-XCV89', 500,'2019-04-05'), ('KEYBOARD','KEYB-567', 100,'2019-05-19'), ('MOUSE','MM-ER56', 250,'2019-05-15'), ('PEN DRIVE','PD-20ET', 400,'2019-07-25'), ('RADEON GRAPHIC CARD',null, 478,'2019-09-19')
Now we have a sales table and it contains five records as shown below.
SELECT * FROM dbo.SALES
Delete specific records from a table
Following delete statement deletes a record from table Sales for SALES_ID =5.
DELETE FROM dbo.SALES WHERE SALES_ID = 5
Lets check the record in table for sales_Id =5
As you can see, it returns blank result that means record for SALES_ID =5 is deleted from table.
Delete command with resetting an identity value
Delete command does not reset values for an identity column means an Identity column in a table is not reset when records are deleted from a table.
As you know, in SALES table we have an identity column that is SALES_ID, that you can also check using SP_HELP stored procedure.
SP_HELP SALES
As you can see, SALES_ID have an identity with seed value 1, means value for SALES_ID column is increased by 1 on every new record entry in table SALES.
As you have deleted a record for SALES_ID = 5 from a table SALES recently, now there are four records left in a table, and last Sales_id value is 4.
Lets check the table records to confirm, and you can see there are four records in a table and Max SALES_ID value is 4.
Now, what happens when you insert a new record in table , Ideally value for Sales_id column for a new record entry should be 5 as the last Sales_id value for an identity column in a table that we can see in table is 4 so expected next value should be 5 (4 +1) that is incremented by 1.
Remember you have deleted a record for Sales_id =5 and delete command deletes the records from a table but does not reset a value for an identity column SALES_ID, So when you insert a new record in a table the value for a Sales_id in this case will be be 6 i.e., 5+1 (including the Sales_id value of deleted record as well) .
Lets insert a one record in table.
INSERT INTO dbo.SALES
(ITEM, ITEM_CODE, ITEM_QTY, PURCHASED_DATE)
VALUES('LAPTOP- ACER','ACER-ACV89', 400,'2019-07-05')
As you can see, SALES_ID value for new record is 6 , instead of 5. Which means delete command just delete the records from table but does not reset a value of an identity column, does not matter if you deletes all the records from table then insert fresh set of new records, the value for an identity column still will be incremented by 1 to last identity value that table had before deleting a records.
Delete command can be rolled back
Delete command supports roll back , Lets look at into through an example.
Execute below scripts , that will delete all records from a table under transaction block, and you see all deleted records are rolled backed immediately after deleting.
BEGIN TRAN SELECT * FROM dbo.SALES DELETE FROM dbo.SALES SELECT * FROM dbo.SALES ROLLBACK TRAN SELECT * FROM dbo.SALES
As you can see, after deleting a records from table Sales, second select statement returns blank data, but when transaction is rolled back , next select statement returns a data which means deleted records can be rolled back.
What happens if you do not provide Where condition in Delete statement ?
If you do not specify where condition in Delete statement, It will delete all records from table.
SELECT * FROM dbo.SALES
You can see, all records has been deleted from table.
1,278 total views, 1 views today