Sunday, 10 February 2013

SQL DELETE Statement Syntax and Example

SQL DELETE Statement
The DELETE statement is used to delete Rows or Records in a table.

Syntax:

DELETE FROM Table_name WHERE some_column=some_value

Note: 
Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

Example:

Suppose we have a EMP Table as follows :

Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1002
Karun
Team Lead
2
35000
1003
Ravi Kumar Rao
Analyst
1
10000
1004
Tamatam
Team Lead
3
25000
1005
Sai Kumar Teja
Analyst
2
15000
1006
William
Manager
3
50000
1007
Vikram Teja Reddy
Analyst
2
180000
1008
Varun
Team Lead
1
33000
1006
Smith
Team Lead
3
50000


Criteria -I :
Deleting a Particular Record :

Delete from EMP WHERE E_Name='Tamatam'

Result :
Emp_ID
E_Name
Job
Dept_ID
Salary
1001
Ravi Teja Reddy
Analyst
1
20000
1002
Karun
Team Lead
2
35000
1003
Ravi Kumar Rao
Analyst
1
10000
1005
Sai Kumar Teja
Analyst
2
15000
1006
William
Manager
3
50000
1007
Vikram Teja Reddy
Analyst
2
180000
1008
Varun
Team Lead
1
33000
1006
Smith
Team Lead
3
50000

Criteria -II :
Deleting a Multiples Records :

Delete from EMP WHERE Emp_ID IN (1001,1003,1005,1007)

Result :
Emp_ID
E_Name
Job
Dept_ID
Salary
1002
Karun
Team Lead
2
35000
1004
Tamatam
Team Lead
3
25000
1006
William
Manager
3
50000
1008
Varun
Team Lead
1
33000
1009
Smith
Team Lead
3
50000

Criteria -III :
To Delete all Records :
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 remain , only Records will be deleted.

Delete* from EMP

Alert: 
Be very careful when deleting records. You cannot undo this statement!

Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts