Record maintenance includes viewing records, looking for one or more records, modifying one or more records, or deleting one or more records. These operations can be performed visually or programmatically using a Data Definition Language (DDL) command.

1. On the Standard toolbar, click the New Query button
2. To create a database, type the following:
    

   USE master; GO IF EXISTS(SELECT name FROM sys.databases WHERE name = N'CeilInn2' ) DROP DATABASE CeilInn2 GO CREATE DATABASE CeilInn2 GO USE CeilInn2; GO CREATE TABLE Rooms ( RoomID int identity(1, 1) NOT NULL, RoomNumber nvarchar(10), RoomType nvarchar(20) default N'Bedroom', BedType nvarchar(40) default N'Queen', Rate money default 75.85, Available bit default 0 ); GO INSERT INTO Rooms(RoomNumber) VALUES(104); GO INSERT INTO Rooms(RoomNumber, BedType, Rate, Available) VALUES(105, N'King', 85.75, 1), (106, N'King', 85.75, 1); GO INSERT INTO Rooms(RoomNumber, Available) VALUES(107, 1); GO INSERT INTO Rooms(RoomNumber, BedType, Rate) VALUES(108, N'King', 85.75); GO INSERT INTO Rooms(RoomNumber, Available) VALUES(109, 1); GO INSERT INTO Rooms(RoomNumber, RoomType, Rate, BedType, Available) VALUES(110, N'Conference', 450.00, N'', 1); GO

3. Press F5 to execute

Before visually performing some operations on a table, you must first select one or more records. In the Table window, to select one record, position the mouse on the left button of the record and click:

To select a range of records, click the gray button of one of the records, press and hold Shift, then click the gray button of the record at the other extreme.

To select the records in a random fashion, select one record, press and hold Ctrl, then click the gray button of each desired record:

To select all records of a table, click the gray button on the left of the first column:

To visually modify one or more records on a table, first open it (you right-click the table in the Object Explorer and click Open Table) to view its records. Locate the record and the field you want to work on and perform the desired operation.

Updating a record consists of changing its value for a particular column. To visually update a record, in the Object Explorer, right-click its table (or view, in some cases) and click Edit Top 200 Rows. This would open the table as a spreadsheet, as seen above. Locate the value under the desired column header, and modify the value as you see fit.

To programmatically update a record, you use a Data Definition Language (DDL) command. If you are working in Microsoft SQL Server:

• In the Object Explorer, 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

The DDL command to update a record is UPDATE. 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.

Consider the following code to create a new database named VideoCollection and to add a table named Videos to it:

CREATE DATABASE VideoCollection;
GO
USE VideoCollection;
GO
CREATE TABLE Videos (
  VideoID INT NOT NULL IDENTITY(1,1),
  VideoTitle nvarchar(120) NOT NULL,
  Director nvarchar(100) NULL,
  YearReleased SMALLINT,
  VideoLength nvarchar(30) NULL,
  Rating nchar(6)
);
GO
INSERT INTO Videos(VideoTitle, Director, YearReleased, VideoLength)
VALUES(N'A Few Good Men','Rob Reiner',1992,'138 Minutes');

INSERT INTO Videos(VideoTitle, Director, VideoLength)
VALUES(N'The Lady Killers', N'Joel Coen & Ethan Coen', N'104 Minutes');

INSERT INTO Videos(VideoTitle, Director, YearReleased, VideoLength)
VALUES(N'The Silence of the Lambs','Jonathan Demme',1991,'118 Minutes');

INSERT INTO Videos(VideoTitle, Director, VideoLength)
VALUES(N'The Distinguished Gentleman', N'James Groeling', N'112 Minutes');

INSERT INTO Videos(VideoTitle, Director, VideoLength)
VALUES(N'Ghosts of Mississippi', N'Rob Reiner', N'130 Minutes');
GO

Imagine you want to indicate that all these videos are rated R. To do this, in our formula, specify the table name. In the SET expression, specify the column name as Rating and assign it R as a string. This would be done as follows:

USE VideoCollection;
GO

UPDATE Videos
SET Rating = N'R';
GO 

If you use the UPDATE statement like this, it acts on all records. The above code would produce:

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.

To edit a record, 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 = N'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 didn't need this change. This is where the identity column becomes valuable. We saw earlier that, when using it with the IDENTITY feature, the interpreter appends a unique value to each record. You can then use that value to identify a particular record because you are certain the value is unique.

Here is an example used to specify the missing copyright year of a particular record:

UPDATE Videos
SET YearReleased = 1996
WHERE VideoID = 5;
GO

Here is an example used to change the name of the director of a particular video:

UPDATE Videos
SET Director = N'Jonathan Lynn'
WHERE VideoTitle = N'The Distinguished Gentleman';

1. Press Ctrl + A to select everything (the code) in the window and, to update a record, type the following:
    

   USE CeilInn2; GO UPDATE Rooms SET BedType = N'Queen' WHERE RoomNumber = 108; GO

2. Press F5 to execute
3. Press Ctrl + A to select the code in the window and type the following:
    

   USE CeilInn2; GO UPDATE Rooms SET Available = 0 WHERE RoomNumber = 109; GO

4. Press F5 to execute

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 are 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.

1. Press Ctrl + A to select the code in the window
2. To update some records and show which ones were changed, type the following:
    

   USE CeilInn2; GO UPDATE Rooms SET Rate = 95.50 OUTPUT INSERTED.* WHERE BedType = N'Queen'; GO

3. Press F5 to execute

If you think all records of a particular table are, or have become, useless, you can clear the whole table, which would still keep its structure. To delete all records from a table, 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.

The DDL command to clear a table of all records is DELETE. It uses 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 remove a record from a table, you can right-click its gray box and click Delete. You can also first select the record and press Delete. You would receive a warning to confirm your intention.

To programmatically delete a record:

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

In SQL, to delete a record, use the DELETE FROM statement associate 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. Once again, 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:

DELETE FROM Videos
WHERE VideoTitle = N'The Lady Killers';

Here is an example used to clear the table of all videos:

DELETE FROM Videos;

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:

USE VideoCollection6;
GO

DELETE FROM Videos
OUTPUT deleted.*
GO

To show the list of the records that were deleted based on a condition, use the following formula:

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

 Here is an example:

USE VideoCollection6;
GO

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

1. Close the query window without saving the file
2. In the Object Explorer, under the Databases node, right-click WorldStatistics and click Delete
3. In the dialog box, click OK
4. In the Object Explorer, under the Databases node, right-click CeilInn2 and click Delete
5. In the dialog box, click OK