This is used to delete rows from the table with condition in the query.
Syntax:
delete [top unsigned_integer] [from] [[database.]owner.]{view_name|table_name} [where search_conditions] [plan "abstract plan"] delete [[database.]owner.]{table_name | view_name} [from [[database.]owner.]{view_name [readpast]| table_name [(index {index_name | table_name} [prefetch size][lru|mru])]} [readpast] [, [[database.]owner.]{view_name [readpast]| table_name [(index {index_name | table_name} [prefetch size][lru|mru])] [readpast]} ...] [where search_conditions]] [plan "abstract plan"] delete [from] [[database.]owner.]{table_name|view_name} where current of cursor_name
1) Create table employee as shown below
CREATE TABLE employee (EMPNO NUMERIC(4) NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR NUMERIC(4), HIREDATE DATETIME, SAL NUMERIC(7, 2), COMM NUMERIC(7, 2) null, DEPTNO NUMERIC(2))
2. Insert data into above table employee.
INSERT INTO employee VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20) INSERT INTO employee VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-28', 1600, 300, 30) INSERT INTO employee VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30) INSERT INTO employee VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-2', 2975, NULL, 20) INSERT INTO employee VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30) INSERT INTO employee VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-1', 2850, NULL, 30) INSERT INTO employee VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-9', 2450, NULL, 10) INSERT INTO employee VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20) INSERT INTO employee VALUES (7839, 'KING', 'PRESIDENT', 7888, '1981-11-17', 5000, NULL, 10) INSERT INTO employee VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30) INSERT INTO employee VALUES (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20) INSERT INTO employee VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30) INSERT INTO employee VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20) INSERT INTO employee VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10)
3. Now, delete rows deptno equals to 10.
delete from employee where DEPTNO=20
4. If we want to delete all the rows from the table employee
delete from employee