Delete Data from Database Using MySQL Native Interface

This example shows how to delete data from a database using MATLAB®. Create the SQL statement using deletion SQL syntax; consult your database documentation for the correct syntax. Execute the delete operation on your database using the execute function with the SQL statement. This example demonstrates deleting records from a MySQL® database.

Create Database Connection

Create a MySQL native interface database connection to a MySQL database using the data source name, user name, and password.

datasource = "MySQLDataSource";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);

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)

ans=3×4 table
    productNumber    Quantity    Price        inventoryDate    
    _____________    ________    _____    _____________________

         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 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)

ans=3×4 table
    productNumber    Quantity    Price        inventoryDate    
    _____________    ________    _____    _____________________

         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"

Close Database Connection

close(conn)