DATABASELINK TUTORIAL

Deleting Data with Raw SQL
This feature is not supported on the Wolfram Cloud.

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

In[1]:=
Click for copyable input

As discussed in "Creating Tables with Raw SQL", the ADDRESSES and MAILER tables should be created.

In[2]:=
Click for copyable input

Here are the contents of the ADDRESSES table.

In[6]:=
Click for copyable input
Out[6]=

Here are the contents of the MAILER table.

In[7]:=
Click for copyable input
Out[7]=

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.

In[8]:=
Click for copyable input
Out[8]=

A SELECT statement verifies that the data has been changed in the table.

In[9]:=
Click for copyable input
Out[9]=

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.

In[10]:=
Click for copyable input
In[11]:=
Click for copyable input
Out[11]=

A SELECT statement verifies that the data has been changed in the table.

In[12]:=
Click for copyable input
Out[12]=

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.

In[13]:=
Click for copyable input
Out[13]=

This deletes the tables and closes the connection.

In[14]:=
Click for copyable input