Record Maintenance: Deleting Records

To visually delete all records from a table, open it in design view, first select all of them, and press Delete. You would receive a warning. If you still want to delete the records, click Yes. If you change your mind, click No.

Using SQL, to clear a table of all records, use the DELETE operator with the following formula:

DELETE TableName;

When this statement is executed, all records from the TableName factor would be removed from the table. Be careful when doing this because once the records have been deleted, you cannot get them back.

If you find out that a record is not necessary, not anymore, or is misplaced, you can remove it from a table.

To visually delete a record in SQL in Microsoft SQL Server Management Studio or Microsoft Visual Studio, open the table to show its records. On the table, you can right-click the gray box on the left of a record and click Delete:

You can also first select the record and press Delete. You would receive a warning to confirm your intention.

In SQL, to delete a record, use the DELETE FROM statement associated with the WHERE operator. The formula to follow is:

DELETE FROM TableName
WHERE Condition(s)

The TableName factor is used to identify a table whose record(s) would be removed.

The Condition(s) factor allows you to identify a record or a group of records that carries a criterion. Make sure you are precise in your criteria so you would not delete the wrong record(s).

Here is an example used to remove a particular record from the table:

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;"))
    {
	SqlCommand command =
		new SqlCommand("DELETE FROM Videos " +
			       "WHERE VideoTitle = 'Lady Killers (The)';",
				connection);
	connection.Open();
	command.ExecuteNonQuery();
    }
}

Instead of one, you can delete more than one record at a time. To do this, first select the group of records, either in a range or at random, then either right-click the selection and click Delete or press Delete:

After clicking Delete, you would receive a warning. If you still want to delete the records, you can click OK.

To programmatically delete a group or records, apply the DELETE FROM table formula and use a WHERE condition that can identify each one of the records.

When some record(s) has(have) been deleted, the operation is performed behind the scenes and you don't see the result. If you want to see a list of the records that were deleted, you can use the OUTPUT operator to display the result. To show the list of the records from a table that was completely emptied, you can use the following formula:

DELETE FROM TableName
OUTPUT DELETED.Columns

The OUTPUT INSERTED expression follows the description we have seen for the record update. Here is an example:

DELETE FROM Videos
OUTPUT deleted.*;

If you want to store the list of records that were deleted, you can use another table. In this case, after DELECTED.*, type INTO followed by the name of the table that will receive the records. Here is an example:

void DeleteAndArchive()
{
    using (SqlConnection connection =
               new SqlConnection("Data Source=(local);" +
                         "Database='VideoCollection1';" +
                         "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("DELETE FROM Videos " +
                               "OUTPUT deleted.* INTO DeletedVideos;",
                               connection);

        connection.Open();
        command.ExecuteNonQuery();
        MessageBox.Show("The record for 'Wall Street' has been updated and archived.",
                        "Video Collection",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    }
}

To show the list of the records that were deleted based on a condition, after the OUTPUT statement, add a WHERE condition. The formula to follow is:

DELETE FROM TableName
OUTPUT DELETED.Columns
WHERE Condition(s)

 Here is an example:

DELETE FROM Videos
OUTPUT deleted.*
WHERE CopyrightYear IS NULL;
GO

Once again, remember that if you want to keep track of the records that get deleted, use another table that can receive those records. Here is an example:

void DeleteAndArchive()
{
    using (SqlConnection connection =
               new SqlConnection("Data Source=(local);" +
                         "Database='VideoCollection1';" +
                         "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("DELETE FROM Videos " +
                               "OUTPUT deleted.* INTO DeletedRVideos " +
                               "WHERE Rating = N'R';",
                               connection);

        connection.Open();
        command.ExecuteNonQuery();
        MessageBox.Show("The record for 'Wall Street' has been updated and archived.",
                        "Video Collection",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
    }
}