Data Maintenance

Deleting Records

Introduction

Like databases, tables, and columns, records need maintenance too. Some of the operations you can perform include deleting a whole record, changing the value of a record under a particular column (which is equivalent to editing a record), etc. Just as done for a column, before changing anything on a record, you must locate it first. This operation is somehow different than the maintenance performed on databases, tables, and columns. The reason is that, although you always know the name of a database, the name of a table, or the name of a column, when it comes to records, you cannot know in advance the information it holds. For this reason, you must use additional operators to help you locate a record. Fortunately, as always, you have various options.

Deleting a record consists of removing it from a table, this includes all entries, if any, under each column for a particular row.

If you are working from SQL Server Enterprise Manager, before removing a record, first display the table in a view that shows its record. You can do this by right-click the table, positioning the mouse on Open Table, and clicking Return All Rows. If you are working in the Server Explorer, to display the table and show all records, double-click the table. In both cases, once in the Data window, to remove a record, right-click the gray box on the left side of the record and click Delete:

After clicking Delete, you would receive a warning message box:

If you still want to continue, you can click Yes and that record would disappear. If you want to change your mind, click No.

To remove a range of records from a table, you can click and drag from one gray box at one end of the range to the gray box at the other end of the range. As an alternative, you can click a gray box at one end of the range, press and hold Shift, then click the gray box at the other other end of the desired range. Once the selection is made, right-click anywhere in the selection and click Delete:

If you click Delete, you would receive a warning that lets you know the number of records that would be deleted. If you still want to delete them, you can click Yes. To change your mind, click No.

To remove all records from a table, you must first select all of them. To do this, you can click the gray box on the left of the first (or the last) record, press and hold Shift, then click the gray box of the last (or first) record:

Once the selection is made, right-click anywhere in the table and click Delete. You would receive the same type of warning for a range of records and you can proceed the same way.

The SQL code that deletes all records from a table uses the following formula:

DELETE TableName

When you create this statement, provide the name of the table as TableName. When you execute this statement, all records of the table would be removed.

To remove one particular record from a table, use the following formula:

DELETE TableName

WHERE CriteriaToFindTheRecord

The DELETE and the WHERE keywords are required. The TableName factor allows you to specify the name of the table that the record belongs to. In order to delete the record, you must provide a way to locate it. Consider the following table named Videos from a database named VideoCollection:

Imagine that you want to remove the record whose video title is "The Silence of the Lambs". In this case, the TableName is Videos. The criterion to find the correct record is that the VideoTitle value of that record = The Silence of the Lambs. To remove it, you would use code as follows:

USE VideoCollection

GO

/* Code used to remove the video titled

   The Silence of the Lambs */

DELETE Videos

WHERE VideoTitle = 'The Silence of the Lambs'

GO

If you use the DELETE formula to remove a record, notice that, as always in SQL Query Analyzer, you would not be warned.

To programmatically delete a record, create a DELETE statement using the same rules we reviewed for SQL Query Analyzer, pass it to a SqlCommand object, and execute the statement by calling the SqlCommand::ExecuteNonQuery() method.

Updating Records  

Updating a Record in the Enterprise Manager

To change a record in SQL Server Enterprise Manager, first open the table with the view that displays records (Right-click the table -> Open Table -> Return All Rows). In the Data window, locate the value you want to change, click it, edit it, and then click somewhere else:

Once the cell loses focus, the new value is automatically saved:

The SQL statement used to change the value of a record uses the following formula:

UPDATE TableName

	SET ColumnName = NewValue

	WHERE CriteriaToLocateRecord

The UPDATE keyword allows you to specify the name of the table whose record you want to change. The table is identified with the TableName factor of our formula.

The SET keyword allows you to identify the column under which exists the value you want to change. The column is identified as ColumnName in our formula. On the right side of the column name, type the assignment operator, followed by the value you want the cell to hold. If the update is successful, the value stored under that column would be replaced.

The WHERE clause allows you to specify the criterion used to locate the particular record that the existing value belongs to.

Consider you have the above table, imagine that, on the video titled "The Distinguished Gentleman", you want to change the name of the director from "Jonathan Line" to "Jonathan Lynn. The table name is Videos. The column that owns the value is named Director. The criterion to use is to identify the record whose VideoTitle is "The Distinguished Gentleman". The code to perform this update would be:

USE VideoCollection GO -- Code used to change the name of a director UPDATE Videos SET Director = 'Jonathan Lynn' WHERE VideoTitle = 'The Distinguished Gentleman' GO

Once again, remember that when performing an operation in the SQL Query Analyzer, you would not be warned.

To update a record in a Windows Forms Application, create an UPDATE statement using the same rules we reviewed for SQL Query Analyzer, pass it to a SqlCommand object before executing the statement with a call to the SqlCommand::ExecuteNonQuery() method.