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 where clause.

Delete data with ROWID

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