Delete Data from SQLite Database

This example shows how to delete data from an SQLite database using the MATLAB® interface to SQLite. Create the SQL statement using deletion SQL syntax. Execute the delete operation on your database using the execute function with the SQL statement.

Create SQLite Connection

Create the SQLite connection conn to the existing SQLite database file tutorial.db. The SQLite connection is an sqlite object.

dbfile = "tutorial.db";
conn = sqlite(dbfile);

The SQL query sqlquery selects all rows of data in the table inventoryTable. Execute this SQL query using the database connection. Import the data from the executed query using the fetch function, and display the last few rows.

sqlquery = "SELECT * FROM inventoryTable";
data = fetch(conn,sqlquery);
tail(data,3)

    productNumber    Quantity    Price         inventoryDate     
    _____________    ________    _____    _______________________

         11             567      11.2     "9/11/2012 12:30:24 AM"
         12            1278      22.3     "10/29/2010 6:17:47 PM"
         13            1700      16.8     "5/24/2009 10:58:59 AM"

Delete Specific Record

Delete the record for the product number 13 from the table inventoryTable. Specify the product number using the WHERE clause in the SQL statement sqlquery.

sqlquery = "DELETE FROM inventoryTable WHERE productnumber = 13";
execute(conn,sqlquery)

Display the data in the table inventorytable after the deletion. The record with product number 13 is missing.

sqlquery = "SELECT * FROM inventoryTable";
data = fetch(conn,sqlquery);
tail(data,3)

    productNumber    Quantity    Price         inventoryDate     
    _____________    ________    _____    _______________________

         10             723      24.3     "3/14/2012 1:13:09 PM" 
         11             567      11.2     "9/11/2012 12:30:24 AM"
         12            1278      22.3     "10/29/2010 6:17:47 PM"

Insert the record back in to maintain the dataset.

data = table(13,1700,16.8,"5/24/2009 10:58:59 AM", ...
    'VariableNames',["productNumber" ...
    "Quantity" "Price" "inventoryDate"]);
sqlwrite(conn,"inventoryTable",data)

Close Database Connection

close(conn)