Record Maintenance: Updating Records

To update a record using SQL:

• In the Object Explorer of Microsoft SQL Server Management Studio, you can right the table, position the mouse on Script Table As -> UPDATE To -> New Query Editor Window
• Open an empty query window and type your code

To support record maintenance operations, the SQL provides the UPDATE keyword that is used to specify the table on which you want to maintain the record(s). The basic formula to use is:

UPDATE TableName
SET ColumnName = Expression

With this formula, you must specify the name of the involved table as the TableName factor of our formula. The SET statement allows you to specify a new value, Expression, for the field under the ColumnName column.

Imagine that, at one time, on a particular table, all records need to receive a new value under one particular column or certain columns. There is no particular way to visually update all records of a table. You can just open the table to view its records, and then change them one at a time.

In SQL, the primary formula of the UPDATE statement as introduced on our formula can be used to update all records. Here is an example:

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='VideoCollection1';" +
			  "Integrated Security=yes;"))
    {
	SqlCommand command =
	   new SqlCommand("UPDATE Videos SET Rating = N'R';",
			  connection);
	connection.Open();
	command.ExecuteNonQuery();

 	MessageBox.Show("All video records have been rated R.");
    }
}

With this code, all records of the Videos table will have their Rating fields set to a value of R.

Editing a record consists of changing a value in a field. It could be that the field is empty, such as the � Year of the the 'The Lady Killers' video of the following table. It could be that the value is wrong, such as the Director of the the 'The Distinguished Gentleman' video of this table:

To edit a record, first open the table to view its records. Locate the record, the column on which you want to work, and locate the value you want to change, then change it.

In SQL, you must provide a way for the interpreter to locate the record. To do this, you would associate the WHERE operator in an UPDATE statement using the following formula:

UPDATE TableName
SET ColumnName = Expression
WHERE Condition(s)

The WHERE operator allows you to specify how the particular record involved would be identified. It is  very important, in most cases, that the criterion used be able to uniquely identify the record. In the above table, imagine that you ask the interpreter to change the released year to 1996 where the director of the video is Rob Reiner. The UPDATE statement would be written as follows:

UPDATE Videos
SET CopyrightYear = 1996
WHERE Rating = 'PG-13';

When this statement is executed, all records whose rating is set to PG-13 will have their year released set to 1996. This may compromise existing records that did not need this change. Therefore, make sure your WHERE statement would isolate one particular record or only those that need to be updated. Here is an example used to change the name of the director of a particular video:

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='VideoCollection1';" +
			  "Integrated Security=yes;"))
    {
	SqlCommand command =
	    new SqlCommand("UPDATE Videos " +
		           "SET WideScreen = '0' " +
		           "WHERE Title = N'Ghosts of Mississippi';",
	connection);
	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show("The video has been updated.");
    }
}

Consider the following table named Employees:

CREATE TABLE Employees
(
	EmployeeNumber int,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	HourlySalary money
);
GO

INSERT INTO Employees
VALUES(283007, N'Megan',    N'Wright',    8.50),
      (480295, N'Horace',   N'Taylor',   20.25),
      (804805, N'Marc',     N'Knights',  10.85),
      (294117, N'Bryan',    N'Tenant',   30.25),
      (837479, N'Paul',     N'Motto',    18.02),
      (280304, N'Joyce',    N'Holliday', 11.66),
      (924802, N'Peter',    N'Mukoko',    8.68),
      (725381, N'Marianne', N'Brooks',   22.64);
GO

By default, the UPDATE statement checks all records of a table to see which one(s) need(s) to be changed. As an alternative, you can ask the database engine to check only a specify range  of the first records of a table based on a percentage of the number of records. To do this, the formula to use is:

UPDATE TOP (Number) PERCENT TableName
SET ColumnName = Expression
WHERE Condition(s)

Based on this formula, after the UPDATE keyword, use TOP, followed by parentheses, and followed by PERCENT. In the parentheses, enter a value between 0.00 and 100.00 of the percentage of records to check. For example, imagine you want to change the hourly salary of the Employees table but you want to make the changes only for the first 40% of records. You can create an UPDATE expression as follows:

UPDATE TOP (40) PERCENT Employees
SET HourlySalary = 12.50
WHERE HourlySalary < 12.50;
GO

This code asks  the database engine to change the hourly salary of the first 40% employees. Since the table contains 8 records, the top 40% produces 8 / (100 / 40) = 8 / 2.5 = 3.2. Rounding to the highest integer, the expression produces 4. As a result, 4 and only the first 4 records are checked. Even though there are records beyond the fourth where the hourly salary is less than 12.50, only the first four records are checked.

After making changes to a table using SQL, you don't get a visual display of what happened. With Transact-SQL, you can temporarily display the result of this operation or you can store it in a table. We already saw how to do this when creating records. You follow the same formula when updating records. The formula is:

UPDATE TableName
SET ColumnName = Expression
OUTPUT INSERTED.Columns
VALUES(Value_1, Value_2, Value_X)

Besides the formula we have used so far, after the SET expression, start with an OUTPUT INSERTED expression, followed by a period. If you want to show all columns of the table, add the * operator. Otherwise, type INSERTED followed by a period, followed by the name of the column you want to show.

Here is an example:

void UpdateAndArchive()
{
    using (SqlConnection connection =
               new SqlConnection("Data Source=(local);" +
                         "Database='VideoCollection1';" +
                         "Integrated Security=yes;"))
    {
        SqlCommand command =
                new SqlCommand("UPDATE Videos " +
                               "SET CopyrightYear = 1987 " +
                               "OUTPUT inserted.* INTO Archives " +
                               "WHERE Title = N'Wall Street';",
                               connection);

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