Data inserted or updated can be deleted if it is not further required in a database. To do this you need the DELETE statement of DML sublanguage of SQL. The DELETE statement when used on a table permanently deletes rows of data satisfying the condition. In this tutorial you will learn to delete data from a table
Syntax of DELETE Statement to Delete Data from a Table
- DELETE- keyword
- table-name is the name of the table from which you want to delete data
- WHERE- keyword (Clause of DELETE statement)
- Condition- is the logical expression. If it is true data rows are deleted from the table
Example-Delete Data from a Table with DELETE Statement
These are the table creation and Insertion statements for DELETE statement examples
CREATE TABLE SalesTbl (SPID number(6) primary Key, SPNAME varchar2(30), SPCITY varchar2(30) DEFAULT 'New York', SPCOMM number(2,2) ); CREATE TABLE CustomerTbl ( CustID number(4) Primary Key, CustName varchar2(50), CustCity varchar2(30), CustPhone varchar2(15), CustCreditScore number(4) ); CREATE TABLE OrdersTbl (OrderID number(4)primary key, CustID number(4) references CustomerTbl (CustID ), SPID number(6) references SalesTbl(SPID ), SaleAMT number(8,2), OrderDate date, DeliDate date ); insert into salesTbl values(1001, 'Bob Mathews', 'Nashville',0.1); insert into salesTbl values(1002,'Cathy Mills','San Diego',0.1); insert into salesTbl values(1003, 'Alex Rode ', 'New York',0.2); insert into salesTbl values(1004, 'Rob Millions ', 'New York',0.2); insert into customertbl values (101,'Sid Vauly','Chicago','013-1222-1919',9.2); insert into customertbl values (102,'Stefan Mook','Ohio','010-8222-1919',8.2); insert into Orderstbl values (1, 101,1003,500.25,'06-MAY-19','18-MAY-19'); insert into Orderstbl values (2, 102,1002,9000.35,'05-MAY-19','12-MAY-19'); insert into Orderstbl values (3, 101,1003,1500.25,'04-MAY-19','18-MAY-19'); insert into Orderstbl values (4, 102,1002,9800.35,'04-MAY-19','20-MAY-19');
Deleting All Rows of a Table
While deleting all data rows from a table you don’t need to specify the column names. You just have to use the table name without WHERE condition.
This statement will delete all rows from OrdersTbl
Deleting Selected Rows of a table
In order to delete only selected data rows of a table you must specify the name of the table along with the condition in the WHERE clause.
DELETE orderstbl WHERE OrderID=2;
This command deletes the data row in table orderstbl having OrderID value 2.
DELETE orderstbl WHERE OrderDate= '04-MAY-19';
This command deletes the data rows in table orderstbl having OrderDate value’04-MAY-19′.
Deleting Rows from a Table Referenced by another Table by Foreign Key
Error: ORA-02292: integrity constraint (SQL_KJRICVLCWXIDIBFYQVBBATXPG.SYS_C0014570995) violated – child record found ORA-06512: at “SYS.DBMS_SQL”, line 1721
This error indicates that we are trying to delete rows from a table which is referenced by another table OrdersTbl
The combination of following two delete statements will overcome this error. We delete rows from referencing table before the referenced table
DELETE OrdersTbl; DELETE CustomerTbl;
This command deletes all the data rows in table Orderstbl. Once no child rows are present in the referencing table, you can now delete the CustomerTbl without error.
Points to remember
To delete data from a table remember following things
- You cannot delete values from specific columns of the table. The DELETE statement deletes complete rows not column data. If you need to delete data from columns, use the UPDATE statement. With UPDATE statement set the values of those columns as NULL or blank (‘ ‘) or zero.
- If you are deleting data in a table having Primary Key defined as Foreign Key in another table then you have to first delete the dependent rows in the referencing table.
- With one DELETE Statement you can delete all or some rows from a table.
- To delete rows satisfying a condition, define the required condition in WHERE Clause.