Deleting Data with Raw SQL
The raw SQL command DELETE deletes data from a database. An alternative is to use the Wolfram Language command SQLDelete, described in "Deleting Data".
If you find that the examples in this tutorial do not work as shown, you may need to install or restore the example database with the "DatabaseLink`DatabaseExamples`" package, as described in "Using the Example Databases".
When deleting data, the result of SQLExecute is an integer specifying the number of rows affected by the query.
Here is an example that removes data. This loads DatabaseLink and connects to the demo database.
As discussed in "Creating Tables with Raw SQL", the ADDRESSES and MAILER tables should be created.
Here are the contents of the ADDRESSES table.
Here are the contents of the MAILER table.
This executes an SQL statement that deletes a row in the MAILER table. It deletes any rows for which the value in the SENDMAILER column is 0 (or False). Delete statements can be created using conditions that depend on the values in columns. Since one row has been deleted, the result is 1.
A SELECT statement verifies that the data has been changed in the table.
Using prepared statements, you can dynamically create SQL statements that delete data within the database. You can combine this with a simple Wolfram Language function. This example deletes an address for a particular user.
A SELECT statement verifies that the data has been changed in the table.
Any restrictions on the values in tables also apply when data is deleted. Thus, if you try to delete an ADDRESS value that is referenced by an item in the MAILER table, an error occurs.
