Programmer need to delete data from the database when it is no longer required. The smallest unit that can be deleted from a database is a row. You can delete a row from a table by using the DELETE DML statement. The syntax of the DELETE statement is:
DELETE [FROM] table_name
[FROM table (s)]
[WHERE condition]
Where,
For example, the following statement deletes the address details of AddressID 104 from the Address table:
DELETE Address
WHERE AddressID = ‘104’
Consider the example of the Adventure Works. The Employee table contains data of those employees who have retired from the company. This data is not required anymore. This increases the size of the database.
You are required to ensure that this old data is removed from the Employee table. You can delete this data by using the following SQL statement:
DELETE FROM HumanResources.Employee
WHERE BirthDate < dateadd (yy, -60, getdate ())
The database contains tables related to the Employee table. The related tables are HumanResources.EmployeeAddress, HumanResources.EmployeeDepartmentHistory, HumanResources.EmployeePayHistory, and HumanResources.JobCandidate. The EmployeeID attribute in these tables is a foreign key to the EmployeeID attribute of the Employee table. Therefore, the query results in an error. Therefore, you need to delete data from the related tables before executing the preceding DELETE statement.
DELETE table_name
You can also use the TRUNCATE DML statement. The syntax of the TRUNCATE statement is:
TRUNCATE TABLE table_name
Where,
TRANCATE TABLE Address
Manipulate XML Data and Parsing with XML document.
DELETE [FROM] table_name
[FROM table (s)]
[WHERE condition]
Where,
- Table_name specifies the name of the table from which you have to delete rows.
- Table_name specifies the name of the table(s) required to set the condition for deletion.
- Condition specifies the condition that identifies the row(s) to be deleted.
For example, the following statement deletes the address details of AddressID 104 from the Address table:
DELETE Address
WHERE AddressID = ‘104’
Deleting Data from Related Tables
While deleting records form related tables, you need to ensure that you first delete the records from the table that contain the foreign key and then from the table that contains the primary key.Consider the example of the Adventure Works. The Employee table contains data of those employees who have retired from the company. This data is not required anymore. This increases the size of the database.
You are required to ensure that this old data is removed from the Employee table. You can delete this data by using the following SQL statement:
DELETE FROM HumanResources.Employee
WHERE BirthDate < dateadd (yy, -60, getdate ())
The database contains tables related to the Employee table. The related tables are HumanResources.EmployeeAddress, HumanResources.EmployeeDepartmentHistory, HumanResources.EmployeePayHistory, and HumanResources.JobCandidate. The EmployeeID attribute in these tables is a foreign key to the EmployeeID attribute of the Employee table. Therefore, the query results in an error. Therefore, you need to delete data from the related tables before executing the preceding DELETE statement.
Deleting All the Records from a Table
As a database developer, you might need to delete all the records from a table. You can do this by using the following DELETE statement:DELETE table_name
You can also use the TRUNCATE DML statement. The syntax of the TRUNCATE statement is:
TRUNCATE TABLE table_name
Where,
- Table_name specifies the name of the table from which you have to delete rows. However, TRUNCATE TABLE is executed faster.
TRUNCATE TABLE does not support the WHERE clause. In addition, the TRUNCATE TABLE statement does no fire a trigger. When truncate is used, the deleted rows are not entered in the transaction log.For example, the following statement deletes all the records from the Address table:
TRANCATE TABLE Address
Manipulate XML Data and Parsing with XML document.