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)
