5.5 SQLDelete

WARNING: SQLDelete permanently deletes data from a table. SQLDelete can cause information stored in your database to be permanently lost. Make sure you back up all files before using this function. In order to gain access to this functionality, you will need to load the DatabaseAccess`Admin` package.

SQLDelete eliminates all data from a particular table in a database. The table name and column names remain intact.

Be careful using SQLDelete. Once this command is issued, the data is deleted with no hope of retrieval.

In[1]:=

In[2]:=

Out[2]=

In order to use SQLDelete, you must first load the DatabaseAccess`Admin` package.

In[3]:=

In[4]:=

Out[4]=

You can use SQLDelete to empty only certain rows from a table by using a column condition argument as defined in Section 5.7. No output is returned upon the completion of SQLDelete, unless an error occurs. To look at the result of an SQLDelete command, issue an SQLSelect command to view data.

5.5.1 Example

In[5]:=

In this example we'll take advantage of the Autocommit option introduced in Section 3.2. In this way no changes that we make will permanently affect the integrity of the database.

In[6]:=

Out[6]=

Without options, SQLDelete will remove data from an entire table. Try removing all of the data from the publishers table in dbselect.mdb. You can put the original data back into that table with SQLInsert.

Note: SQLDelete cannot take a list of tables. It only works on one table at a time.

Here is the publishers table again. Delete the table's data.

In[7]:=

Out[7]=

In[8]:=

Now view the publishers table with SQLSelect.

In[9]:=

Out[9]=

There is no longer any data in the table. The column names are intact, however.

In[10]:=

Out[10]=

The table is still listed in the data source.

In[11]:=

Out[11]=

SQLInsert will now be used to restore the publishers table to the form found in Section 5.4. There were three rows in the publishers table, so three rows will be inserted.

In[12]:=

In[13]:=

In[14]:=

Now view the table once more.

In[15]:=

Out[15]//TableForm=

In[16]:=