A complex archiving program may need to delete rows depending on the contents of a temporary table which is only visible to the current session. Compiling and deploying a C program may be inconvenient or prohibited. The SQL session may be running on a different machine where such a tool is not available or for which such a tool cannot easily be compiled, such as on Windows.
Whatever the reason, stored procedure sp_dbdelete listed after this article might be an attractive alternative. The available parameters are:
You must supply the table name first. If it’s not in the current database, specify with “database:table”. If it’s in a different instance, use “database@instance:table”.
The set of rows to be deleted must also be defined as either:
- conditions within the table affected, starting with keyword WHERE (unlike dbdelete);
- a complete SELECT statement returning integer key values to be deleted.
Either can be upper or lower case. If the second form is used, the key column is assumed to be ROWID unless a column name is provided as a third parameter. Only columns of type INT, INT8, BIGINT, or their SERIAL equivalents are supported at this time. ROWID is always available unless the table has multiple partitions (aka fragments) as was created without.
The last fourth parameter will normally never need specifying. You can perform your own experiments, but 100 seems to be the smallest number of rows per DELETE that doesn’t experience slower performance. Thousands are not possible due to limits on the maximum statement size possible with EXECUTE IMMEDIATE in Stored Procedure Language (SPL) which varies with version and is not well documented.
Return values are:
INT AS selected,
INT AS deleted,
INT AS seconds;
The first two will be the same unless some of the rows selected were no longer there at time of deletion, in which case “deleted” will be slightly less. This would be a concern if using ROWID, as the wrong rows could be deleted as a result on a volatile table.
The run time in seconds is also returned, calculated accurately by fetching the time from a system table before and after. Beware of using CURRENT which is a constant in SPL containing when the procedure was started.
Within SPL, when a SQL statement references an object without specifying the database, it is assumed to be in the same one as the procedure, not the database to which the calling session is connected. This is potentially inconvenient if you want just one copy of the procedure defined, but will be calling it from many application databases in that instance. This is solved in sp_dbdelete as follows:
- If the table name has been specified without a database prefix, the top-level database for the session is determined and applied.
- When supplying an entire SQL statement in the second parameter to fetch key values, prefix object names with “$dbname” if you want that replaced in the same manner.
Do not execute this procedure inside a transaction as that will negate the purpose!
See the DOCUMENT section at the end of the procedure listing for examples of usage.
Should the need arise, a very similar procedure for mass UPDATE could be coded with one extra parameter containing the SET clause.