Home

Record Maintenance: Updating the Records

   

Updating a Record

Updating a record consists of changing its value for a particular column. To visually update a record, open the table to show its records, locate the value that needs to be updated and edit it.

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.

Updating all Records

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:

Updating all Records

Editing a Record

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:

Video Title Director © Year Length Rating
A Few Good Men Rob Reiner 1992 138 Minutes R
The Silence of the Lambs Jonathan Demme 1991 118 Minutes  
The Distinguished Gentleman James Groeling   112 Minutes R
The Lady Killers Joel Coen & Ethan Coen   104 Minutes R
Ghosts of Mississippi Rob Reiner   130 Minutes  

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.");
    }
}

Updating all Records

 

 

Published on Thursday 03 January 2008

 

Previous Copyright © 2007 FunctionX, Inc. Home