Oracle Delete
Examples and syntax explanation
Always test these 'oracle delete' commands on a test server
before implementing them on a production server.
before implementing them on a production server.
The Oracle Delete statement allows you to remove rows from a table.
The syntax is : delete from <table_name> (where <restriction clause>);
If you specify the delete statement without the where clause, you will delete all rows from a table.
Specifying the where clause will restrict the deletion to the rows which match the restriction clause.
Here is the sample table:
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING MELBOURNE
60 IT HARTORD
70 CALLCENTRE
80 SUPPORT NONE
90 BOISE
91 SHIPPING DETROIT
92 FINANCE DETROIT
11 rows selected.
Let's delete all rows that are located in Detroit.
SQL> delete from dept where loc = 'DETROIT';
2 rows deleted.
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING MELBOURNE
60 IT HARTORD
70 CALLCENTRE
80 SUPPORT NONE
90 BOISE
9 rows selected.
Now let's select all rows that have a null value in either the dname column or the deptno column.
SQL> delete from dept where dname is null or loc is null;
2 rows deleted.
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 MARKETING MELBOURNE
60 IT HARTORD
80 SUPPORT NONE
7 rows selected.
Now let's delete all the remaining rows from this table:
SQL> delete from dept; 7 rows deleted. SQL> select * from dept order by deptno; no rows selected
Lastly: do not forget to commit to make your changes permanent after you use the Delete statement.
