Advanced delete with SQL from OpenEdge
We saw how to Delete data from OpenEdge using SQL and ODBC in a previous article. We can delete data using ROW ID and subqueries. This article covers these two simple, yet very useful, means of deleting data from OpenEdge using SQL. Let’s assume that you have a client such as DBeaver set up. Connect to your OpenEdge database using ODBC. In my examples, I am using Sports2000 database.
Delete data with a WHERE clause
Let’s review the departments in department table:
SELECT ROWID, d.* FROM department d WHERE deptcode in ('4000', '6000'); rowid |DeptCode |DeptName | --------------------|---------|---------| 0000000000000000551 |4000 |Test1 | 0000000000000000552 |6000 |Test2 |
Let’s delete department with code 4000.
DELETE FROM department WHERE DeptCode = '4000';
This is a simple example of deleting with a
Delete data with
Different databases have an internal ID to track rows. OpenEdge has a rowid – one for each row. You can read about ROWID’s implementation in Progress’s Article 1685. It is easy to delete data using ROWID.
DELETE FROM department WHERE ROWID = '0000000000000000552';
Delete data with a subquery
Subquery can result in one or more records. In the example below, we are retrieving only 1 record from a subquery and asking the main query to delete said data.
DELETE FROM department WHERE deptcode = (SELECT deptcode FROM department WHERE deptname = 'Test2'); DELETE FROM department WHERE EXISTS (SELECT 1 FROM department d WHERE d.deptname = 'Test2' AND d.deptcode = department.deptcode);
The above queries will delete record of choice.
Deleting an entire table
Deleting all records in a table requires knowledge of
lock table entries. There’s a database configuration called
lock table entries that allows integer value to be set. This integer limits the number of records that can be deleted at a time. The default is 8192. A table with more than 8192 records will require deletion in batches.
Delete data in tables with less than 8192 records with a simple statement like so:
DELETE FROM location WHERE 1=1;
Table with more than 8192 records can be deleted in batches like so:
DELETE FROM location WHERE EXISTS (SELECT 1 FROM location l WHERE l.id = location.id FETCH FIRST 8000 ROWS only);
Repeat the statement above over and over again until there are 0 records deleted.
For SQL usage, it is recommended that lock table entries configuration is set to a high number such as 500000 (500K) so that you can bulk delete/update data.
Truncate table not available in OpenEdge
There is no equivalent of
truncate table in OpenEdge. The closest statement to that is
delete from tablename where 1=1. There is a difference between truncating a table and deleting from a table in different databases. In OpenEdge, we don’t have to worry about such distinctions between truncating a table and deleting data from a table.
Be sure to see other topics listed on Modern web applications and API with OpenEdge, SQL, ODBC and PHP