This is documentation for Mathematica 6, which was
based on an earlier version of the Wolfram Language.
View current documentation (Version 11.2)
DatabaseLink Tutorial

Deleting Data with Raw SQL

The raw SQL command DELETE deletes data from a database. An alternative is to use the Mathematica 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 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.
Click for copyable input
As discussed in "Creating Tables with Raw SQL", the ADDRESSES and MAILER tables should be created.
Click for copyable input
Here are the contents of the ADDRESSES table.
Click for copyable input
Here are the contents of the MAILER table.
Click for copyable input
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.
Click for copyable input
A SELECT statement verifies that the data has been changed in the table.
Click for copyable input
Using prepared statements, you can dynamically create SQL statements that delete data within the database. You can combine this with a simple Mathematica function. This example deletes an address for a particular user.
Click for copyable input
Click for copyable input
A SELECT statement verifies that the data has been changed in the table.
Click for copyable input
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.
Click for copyable input
This deletes the tables and closes the connection.
Click for copyable input