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.
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 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'
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(conn)