DELETE
DELETE#
Syntax#
delete ::=#
from_clause ::=#
where_clause ::=#
returning_clause ::=#
limit_clause ::=#
multiple_delete ::=#
tbl_ref ::=
Prerequisites#
The SYS user, the owner of the schema containing the table, users having the DELETE ANY TABLE system privilege, and users having the DELETE privilege for the specified table can delete rows from tables using this statement.
If the user wishes to delete a record from a view, the user must have the same privileges on the base tables as above.
Description#
This statement is used to remove records that meet the specified conditions, if any, from the specified table. It can also be used to delete data from a specified partition.
The WHERE clause is identical to the WHERE clause of a statement. If it is omitted, all of the data in the table are deleted.
user_name#
This is used to specify the name of the owner of the table from which to delete records. If omitted, Altibase will assume that the table belongs to the schema of the user connected via the current session.
tbl_name#
This is used to specify the name of the table containting the records to delete.
view_name#
This specifies the name of the view from which a record is to be deleted.
subquery#
This specifies the view from which a record is to be deleted as a subquery.
returning_clause#
This retrieves records affected by DML statements.
expr#
Each expr must be a column name affected by DML statements or a data expression compatible with column types.
INTO#
This commands modified record values to be respectively stored as variable_name variables.
variable_name#
Each variable_name is either a host variable or a PSM variable in which queried expr values are to be stored. Unless using record type variables, the number of variables must equal the number of expr of the expr list.
Host variable or PSM variable types must be compatible with relevant expr types.
Note: ':' must be prefixed to variable names for iSQL
Limitations of the returning clause:
- For UPDATE, DELETE and INSERT statements, aggregate functions are not allowed in each expr.
- This clause can only be used for tables.
- LOB types cannot be queried with this clause.
- Aliases or subqueries are not allowed in expr.
- Sequences are not allowed in expr.
Note: Multiple rows can be simultaneously returned as collection variables using the BULK COLLECT clause within PSM. For further information, refer to the Stored Procedures Manual.
multiple_delete#
The following example deletes records that meet the join condition from the table specified in tbl_name.
Limitations of the multiple delete clause:
- limit_clause and returning_clause cannot be used.
- dictionary table cannot be used.
- full outer join canno be used.
HINTS Options#
Please refer to "Hint Syntax" and "Hint List" in the Chapter 2 for more detailed information.
Example#
Deleting Rows#
<Query>Delete all data from a table
DELETE FROM orders;
<Query> Delete partition P2 from Table T1.
DELETE FROM T1 PARTITION (P2);
<Query> Delete the orders the employee "William" received.
DELETE
FROM orders
WHERE eno = (SELECT eno FROM employees
WHERE e_firstname = 'William');
Deleting Rows from Join Views#
<Query> Create a view joining the two tables, employees and departments, and then delete employees belonging to the department 'BUSINESS DEPT'. Data is deleted from the table employees and this is affirmed by comparing the number of rows of the table employees before and after data is deleted.
iSQL> CREATE VIEW simple_emp AS
SELECT e.eno, e.e_lastname, e.salary, d.dname
FROM employees e, departments d
WHERE e.dno = d.dno;
Create success.
iSQL> select * from simple_emp;
ENO E_LASTNAME SALARY DNAME
-----------------------------------------------------------------------------------
3 Kobain 2000 RESEARCH DEVELOPMENT DEPT 1
16 Chen 2300 RESEARCH DEVELOPMENT DEPT 1
6 Momoi 1700 RESEARCH DEVELOPMENT DEPT 2
13 Jones 980 RESEARCH DEVELOPMENT DEPT 2
10 Bae 4000 SOLUTION DEVELOPMENT DEPT
11 Liu 2750 SOLUTION DEVELOPMENT DEPT
14 Miura 2003 SOLUTION DEVELOPMENT DEPT
15 Davenport 1000 SOLUTION DEVELOPMENT DEPT
17 Fubuki 1400 QUALITY ASSURANCE DEPT
4 Foster 1800 CUSTOMERS SUPPORT DEPT
1 Moon PRESALES DEPT
5 Ghorbani 2500 PRESALES DEPT
8 Wang MARKETING DEPT
9 Diaz 1200 MARKETING DEPT
18 Huxley 1900 MARKETING DEPT
7 Fleischer 500 BUSINESS DEPT
12 Hammond 1890 BUSINESS DEPT
19 Marquez 1800 BUSINESS DEPT
20 Blake BUSINESS DEPT
19 rows selected.
iSQL> select count(*) from employees;
COUNT
-----------------------
20
1 row selected.
iSQL> DELETE FROM simple_emp WHERE dname='BUSINESS DEPT';
4 rows deleted.
iSQL> select count(*) from employees;
COUNT
-----------------------
16
1 row selected.
Deleting Multiple table data#
<Query> Delete employees belong to the 'MARKETING DEPT' deparment in the employees and departments tables.
iSQL> DELETE e, d FROM employees e, departments d WHERE e.dno = d.dno and d.dname = 'MARKETING DEPT';
4 rows deleted.
iSQL> select count(*) from employees e, departments d WHERE e.dno = d.dno and d.dname = 'MARKETING DEPT';
COUNT
-----------------------
0
1 row selected.
Deleting Rows using the RETURNING clause#
<Query> The following example returns the values of the deleted rows as output bind variables :v1, :v2.
iSQL> create table employees ( eno integer, ename varchar(20));
Create success.
iSQL> var v1 output integer;
iSQL> var v2 output varchar(30);
iSQL> insert into employees values (1, 'jake');
iSQL> insert into employees values (2, 'nikita');
iSQL> insert into employees values (3, 'dana');
iSQL> prepare delete from employees where eno=2 return eno, ename into :v1, :v2;
1 row deleted.
iSQL> print var
[ HOST VARIABLE ]
-------------------------------------------------------
NAME TYPE VALUE
-------------------------------------------------------
V1 INTEGER 2
V2 VARCHAR(30) nikita