Delete Data from Databases

This example shows how to delete data from your database using MATLAB^®.

Create the SQL statement with your deletion SQL syntax. Consult your database documentation for the correct SQL syntax. Execute the delete operation on your database using the execute function with your SQL statement. This example demonstrates deleting data records in a Microsoft Access^® database.

Connect to Database

Create the database connection conn to a Microsoft Access database using an ODBC driver and the data source name dbdemo. This database contains the table inventorytable with the column productnumber.

conn = database('dbdemo','','');

The SQL query sqlquery selects all rows of data in the table inventorytable. Execute this SQL query using conn. 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)

ans =

  8×4 table

    productnumber    quantity    price        inventorydate    
    _____________    ________    _____    _____________________

          6            4540         8     '2013-12-25 19:45:00'
          7            6034        16     '2014-08-06 08:38:00'
          8            8350         5     '2011-06-18 11:45:35'
          9            2339        13     '2011-02-09 12:50:59'
         10             723        24     '2012-03-14 13:13:09'
         11             567         0     '2012-09-11 00:30:24'
         12            1278         0     '2010-10-29 18:17:47'
         13            1700      14.5     '2009-05-24 10:58:59'

Delete Specific Record

Delete the data 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)

ans =

  8×4 table

    productnumber    quantity    price        inventorydate    
    _____________    ________    _____    _____________________

          5            9000         3     '2012-09-14 15:00:25'
          6            4540         8     '2013-12-25 19:45:00'
          7            6034        16     '2014-08-06 08:38:00'
          8            8350         5     '2011-06-18 11:45:35'
          9            2339        13     '2011-02-09 12:50:59'
         10             723        24     '2012-03-14 13:13:09'
         11             567         0     '2012-09-11 00:30:24'
         12            1278         0     '2010-10-29 18:17:47'
 

Delete Record Using MATLAB Variable

Define a MATLAB variable productID by setting it to the product number 12.

productID = 12;

Delete the data using the MATLAB variable productID. Build an SQL statement sqlquery that combines the SQL for the delete operation with the MATLAB variable. Since the variable is numeric and the SQL statement is a character vector, convert the number to a character vector. Use the num2str function for the conversion. Concatenate the delete SQL statement and the numeric conversion using the square brackets.

sqlquery = ['DELETE * FROM inventorytable WHERE ' ...
    'productnumber = ' num2str(productID)];
execute(conn,sqlquery)

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

sqlquery = 'SELECT * FROM inventorytable';
data = fetch(conn,sqlquery);
tail(data)

ans =

  8×4 table

    productnumber    quantity    price        inventorydate    
    _____________    ________    _____    _____________________

          4            2580        21     '2013-06-08 14:24:33'
          5            9000         3     '2012-09-14 15:00:25'
          6            4540         8     '2013-12-25 19:45:00'
          7            6034        16     '2014-08-06 08:38:00'
          8            8350         5     '2011-06-18 11:45:35'
          9            2339        13     '2011-02-09 12:50:59'
         10             723        24     '2012-03-14 13:13:09'
         11             567         0     '2012-09-11 00:30:24'

Close Database Connection

close(conn)