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.
As discussed in
"Creating Tables with Raw SQL", the
ADDRESSES and
MAILER tables should be created.
Here are the contents of the
ADDRESSES table.
| Out[7]= |  |
Here are the contents of the
MAILER table.
| Out[8]= |  |
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.
| Out[9]= |  |
A SELECT statement verifies that the data has been changed in the table.
| Out[10]= |  |
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.
| Out[12]= |  |
A SELECT statement verifies that the data has been changed in the table.
| Out[13]= |  |
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.
| Out[14]= |  |
This deletes the tables and closes the connection.
