Delete Database Records
DELETE FROM provides a way to remove records from a table. Deleting records from a table works similarly to updating in that the WHERE clause specifies which records will be affected.
Following is the general format for the DELETE statement:
DELETE FROM <table name> WHERE <conditions>
Following are some examples of how the DELETE statement can be used:
DELETE FROM TALENT WHERE _rowid = 100
DELETE FROM TALENT WHERE FIRST_NAME = 'Bill'
You may also want to delete a set of records that have common characteristics. For instance, the following SQL statement will delete all records with a CATEGORY of Drama and RATED of PG.
DELETE FROM MOVIE WHERE CATEGORY = 'Drama' and RATED = 'PG'
Using Subqueries in Deletes
Sometimes it is necessary to delete records that match values in other tables. For this you can use a subquery in the where statement. Here is an example that uses an IN clause to update the values in one table based on the value of a matching record in another.
DELETE FROM MOVIE WHERE STUDIO_ID IN (SELECT STUDIO.STUDIO_ID FROM STUDIO WHERE STUDIO.NAME = ‘MGM’)
Safe SQL Mode
OpenBase provides a “safe SQL Mode” through the database configure panel which will require all update statements to have a where clause. This is a safe-guard when you have a database that could be effected by users mistyping SQL statements.
