The DELETE Statement in SQLite

The DELETE statement is a standard SQL statement that is used to permanently remove one or more records from a table in a SQLite database. The basic syntax is as follows:

DELETE FROM main.tablename

or,

DELETE FROM main.tablename
WHERE Boolean expression

If the WHERE clause is not used, then the entire contents of a given table is deleted.

Using the WHERE Clause

To better control the records that you are deleting, you can use the WHERE clause to specify a Boolean variable. As with other SQL WHERE clauses, you can use the AND keyword to specify more than one column or the OR keyword to specify one column or another. There is no limit on the number of columns that you can add to your WHERE arguments. In addition, you can use NOT, as in NOT IN or NOT EQUAL. You can also use the BETWEEN keyword; for example, to specify a date or number range. By using the WHERE clause along with DELETE, you can limit the number of records that are affected by the query.

The syntax is as follows:

        DELETE FROM table
        WHERE columnA = 'value'
        AND columnB = 'value'

You can also write a query as follows:

        DELETE FROM table
        WHERE (columnA = 'value'
        AND columnB = 'value') OR
        (columnC = 'value'
        AND columnD = 'value')

Restrictions and Triggers

When using the DELETEstatement with a TRIGGER, you are not allowed to use the schema name, only the table name. In other words, you must use the DELETE as follows:

DELETE FROM table

Rather than:

DELETE FROM main.tablename

Also, if the DELETE statement in the trigger is not associated with a TEMP table, then the trigger must be in the same database as the trigger. The trigger will search for tables in each of the attached databases in the order in which the database was attached.

DELETE Limits

You can use the LIMITS clause along with the ORDER BY clause to restrict the number of rows to delete. By using the ORDER BY, you can sort the records in either an ascending (ASC) or descending (DESC) order so as to ensure the proper rows are targeted for deletion.

In order to use the LIMIT and ORDER BY clauses, you must enable the SQLITE_ENABLE_UPDATE_DELETE_LIMIT option when compiling the database. Also, it is important to keep in mind that the limits and triggers are not supported for the DELETE statement in SQLite.

A Swift SQLite Delete Example

Using the SQLite DELETE statement in Swift is very easy, as the following example illustrates. After setting the usual variables and constants, we set up the database in the viewDidLoad function and call the setupSampleTable, addRecords, and sampleDelete functions sequentially.

The first two functions create a table and add one record to the table. The main purpose of this code is to demonstrate the DELETE query in Swift, which I do in the sampleDelete function .

    let dbName:String="chapter8.sqlite"
    var db:COpaquePointer?=nil
    var sqlStatement:COpaquePointer?=nil
    var errMsg: UnsafeMutablePointer<UnsafeMutablePointer<Int8>>! = nil
    internal let SQLITE_STATIC = unsafeBitCast(0, for:sqlite3_destructor_type.self)
    internal let SQLITE_TRANSIENT = unsafeBitCast(-1, for:sqlite3_destructor_type.self)
    var dbPath:URL = URL(fileWithPath:””)
    var errStr:String = ""
    override func viewDidLoad() {
        super.viewDidLoad()
        let dirManager = FileManager.default()
        do {
            let directoryURL = try dirManager.urlForDirectory(FileManager.SearchPathDirectory.documentDirectory, in: FileManager.SearchPathDomainMask.userDomainMask, appropriateForL: nil, create: true)
        dbPath = try! directoryURL.appendingPathComponent(dbName)
        } catch let err as NSError {
            print("Error: (err.domain)")
        }
        self.setupSampleTable()
        self.addRecords()
        self.sampleDelete()

    }

func setupSampleTable(){
        let creatSQL:String = "CREATE TABLE IF NOT EXISTS sample(id int, name varchar)"
        if(sqlite3_open(dbPath.path!, &db)==SQLITE_OK){
            if(sqlite3_prepare_v2(db, creatSQL, -1, &sqlStatement, nil)==SQLITE_OK){
                if(sqlite3_step(sqlStatement)==SQLITE_DONE){
                    print("table created")
                    sqlite3_finalize(sqlStatement)
                    sqlite3_close(db)
                }else{
                    print("unable to create table")
                }
            }
        }
    }
                                                                          
    func addRecords(){
        let insertSQL:String = "INSERT INTO TABLE main.sample (id, name) VALUES(?,?)"
        if(sqlite3_open(dbPath.path!, &db)==SQLITE_OK){
            if(sqlite3_prepare_v2(db, insertSQL, -1, &sqlStatement, nil)==SQLITE_OK){
                sqlite3_bind_int(sqlStatement, 1, 1)
                sqlite3_bind_text(sqlStatement, 2, "kevin", -1, SQLITE_TRANSIENT)
                if(sqlite3_step(sqlStatement)==SQLITE_DONE){
                    print("table created")
                    sqlite3_finalize(sqlStatement)
                    sqlite3_close(db)
                }else{
                    print("unable to create table")
                }
            }
        }
    }

As you can see from the code that follows, the DELETE query follows a pattern similar to that of the other CRUD operations. We first set a SQL query string for the DELETE statement, then we attempt to open the database and load the DELETE SQL query string into memory using the sqlite3_prepare_v2 function.

Then we bind the WHERE value using the sqlite3_bind_int function and execute the query using the sqlite3_step function. If we get a successful status result, we clean up the query with sqlite3_finalize and close the database.

func sampleDelete(){
        let deleteStmt:String = "DELETE FROM sample WHERE id = ?"

         if(sqlite3_open(dbPath.path!, &db)==SQLITE_OK){
            if(sqlite3_prepare_v2(db, deleteStmt, -1, &sqlStatement, nil)==SQLITE_OK){
                sqlite3_bind_int(sqlStatement, 1, 1)
                if(sqlite3_step(sqlStatement)==SQLITE_DONE){
                    print("item deleted")
                    sqlite3_finalize(sqlStatement)
                    sqlite3_close(db)
                }else{
                    print("unable to delete")
                }
            }
         }
    }

Alternatively, we could perform the same query operation using the sqlite3_exec function, as in the following example. As you may remember, the sqlite3_exec function encapsulates the sqlite3_prepare_v2, sqlite3_step, and sqlite3_finalize functions. See here:

func sampleExecDelete(){
        let deleteStmt:String = "DELETE FROM sampleTable WHERE id = ?"
       if(sqlite3_open(dbPath.path!, &db)==SQLITE_OK){
            if(sqlite3_exec(db, deleteStmt, nil, &sqlStatement, errMsg)==SQLITE_OK){
                sqlite3_bind_int(sqlStatement, 1, 1)
                sqlite3_close(db)
            }else{
                print("unable to delete")
        }
    }
}

Adding the Delete Functionality to the Winery App

This is the final installment of the CRUD application that we have been building. To begin adding the DELETE functionality to our app, open the header and the deleteRecords method as shown in the code that follows. The deleteRecords method will take only one parameter for the WHERE clause variable.

To implement the DELETE functionality in the Winery app, we will need to make several modifications to the various view controllers and table view controllers as well as to the storyboard's UITableViewControllers. We will also need to add two functions to the WineryDAO class.

func deleteWineRecord(record:String){
    let deleteSQL = "DELETE FROM wine WHERE name = ?"

    if(sqlite3_open(dbPath.path!, &db)==SQLITE_OK){
        if(sqlite3_prepare_v2(db, deleteSQL, -1, &sqlStatement, nil)==SQLITE_OK){
            sqlite3_bind_text(sqlStatement, 1, record, -1, SQLITE_TRANSIENT)
            if(sqlite3_step(sqlStatement)==SQLITE_DONE){
                print("item deleted")
            }else{
                print("unable to delete")
            }
        }

    }
}

func deleteWineryRecord(record:String){
        let deleteSQL = "DELETE FROM winery WHERE name = ?"
       if(sqlite3_open(dbPath.path!, &db)==SQLITE_OK){
                if(sqlite3_exec(db, deleteSQL, nil, &sqlStatement, errMsg)==SQLITE_OK){
                   sqlite3_bind_text(sqlStatement, 1, record, -1, SQLITE_TRANSIENT)
                   sqlite3_close(db)
                }else{
                  print("unable to delete")
                }
       }
    }                                                                                                    

override func viewDidLoad() {
        super.viewDidLoad()
        loadWineList()

        // Uncomment the following line to display an Edit button in the navigation bar for this view controller.
        self.navigationItem.rightBarButtonItem = self.editButtonItem()
    }

override func tableView(tableView: UITableView, commitEditingStyle editingStyle: UITableViewCellEditingStyle, forRowAtIndexPath indexPath: NSIndexPath) {
if editingStyle == .delete {
            tableView.beginUpdates()
            // Delete the row from the data source
            let winery:Wineries = wineryListArray[(indexPath as NSIndexPath).row]
            tableView.deleteRows(at: [indexPath], with: .fade)
             wineDAO.deleteWineryRecord(winery.name)
            tableView.endUpdates()        } else if editingStyle == .Insert {
            // Create a new instance of the appropriate class, insert it into the array, and add a new row to the table view
        }    
    }

override func viewDidLoad() {
        super.viewDidLoad()
        self.loadWineList()
        self.navigationItem.rightBarButtonItem = self.editButtonItem()
    }

override func tableView(tableView: UITableView, commitEditingStyle editingStyle: UITableViewCellEditingStyle, forRowAtIndexPath indexPath: NSIndexPath) {
        if editingStyle == .delete {
            tableView.beginUpdates()
            let wine = wineListArray[(indexPath as NSIndexPath).row]
            // Delete the row from the data source
            tableView.deleteRows(at: [indexPath], with: .fade)
            wineDAO.deleteWineRecord(wine.name)
            tableView.endUpdates()       
          } else if editingStyle == .Insert {
            // Create a new instance of the appropriate class, insert it into the array, and add a new row to the table view
        }    
    }

Running the App

Figure 8-1 is the list of wines in the Cellar TableViewController. Instead of selecting an item, click on the “Edit” link. This will switch the view into Edit mode.

Figure 8-1. The Cellar TableView

Figure 8-2 shows the Cellar TableView in Edit mode. Notice how the Edit button was converted to Done. All this is provided with one line of code in the viewDidLoad function. Select one of the empty rows, and the row will display a Delete button.

Figure 8-2. The Cellar TableView in Edit mode

Figure 8-3 shows the Delete button for the selected row. If you click on the button, the override tableView ( tableView: UITableView,commitEditingStyle editingStyle: UITableViewCellEditingStyle,forRowAtIndexPath indexPath: NSIndexPath) will be called, and the selected item will be removed from the array using the deleteRowsAtIndexPaths function . As you may remember, we also call deleteWineRecord to delete the record from the database.

Figure 8-3. the Selected item for deletion

Figure 8-4 displays the TableView for the Wineries table view. It currently has only one entry. We will repeat the same process as before and click on the Edit button to switch into Edit mode.

Figure 8-4. The Wineries TableView

As shown in Figure 8-5, with the TableView in Edit mode , you can select the entry, which will trigger the row to display a Delete button. Also notice the "Done" link, which you use to switch the TableView back into Read mode.

Figure 8-5. The Wineries in Edit mode

Figure 8-6 displays the Delete button, and if you click on the button the selected entry will be deleted from both the winery array and the winery table in the database.

Figure 8-6. The Wineries TableView with item selected for deletion

Figure 8-7 displays the absent-entry Wineries TableView.

Figure 8-7. The selected ite m has been deleted

Summary

In this chapter we discussed how to use the SQLite DELETE statement to delete records from a SQLite database. Also how we can use a WHERE clause to limit the number of records to delete or to target a certain record or records.

We also explored how to implement the DELETE statement in Swift 3 and finally we added the delete functionality to the Wine app using Swift. In the next chapter, we will implement searching for records in a SQLite database and displaying those records.