Updating a Record

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 = '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 YearReleased = 1996
WHERE Director = 'Rob Reiner';

In the above table, there are at least two videos directed by Rob Reiner. When this statement is executed, all video records whose director is Rob Reiner would be changed, which would 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 Director = 'Jonathan Lynn' " +
		"WHERE [Video Title] = 'The Distinguished Gentleman';",
	connection);
	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show("The director of 'The Distinguished Gentleman' " +
			"video has been updated.");
    }
}