Delete Statements

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.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License