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

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

%% Close Database Connection

close(conn)

% Copyright 2020 The MathWorks, Inc.