SQL Tutorial - Lesson 10: Records Maintenance

	Records Maintenance

Introduction

Record maintenance includes modifying or deleting records. To support such 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.

Practical Learning: Introducing Record Maintenance

Start your SQL environment
To create a new database, execute the following statement:
CREATE DATABASE VideoCollection;
To make the new database the default, execute the following statement:
USE VideoCollection;

To create a new table, execute the following statement:

CREATE TABLE Videos (
VideoID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
VideoTitle varchar(120) NOT NULL,
Director varchar(100) NULL,
YearReleased SMALLINT,
VideoLength varchar(30) NULL,
Rating varchar(6));

To enter a few records in this table, execute the following statements:

INSERT INTO Videos(VideoTitle, Director, YearReleased, VideoLength)
VALUES('A Few Good Men','Rob Reiner',1992,'138 Minutes');

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

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

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

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

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. The primary formula of the UPDATE statement as introduced on our formula can do just that.

Practical Learning: Updating all Records

To assign the same value to all records of a column, execute the following statement:
UPDATE Videos SET Rating = 'R';

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:

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

It could be that the value is wrong, such as the Director of the the 'The Distinguished Gentleman' video of the above table.

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 = '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 AUTO_INCREMENT (MySQL) or IDENTITY (MSDE) 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.

Practical Learning: Editing and Updating Records

To specify the missing copyright year of a particular record, execute the following statement:
UPDATE Videos SET YearReleased = 1996 WHERE VideoID = 5;

To change the name of the director of a particular video, execute the following statement:

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

Removing all Records

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 clear a table of all records, if you are using MSDE, use the DELETE operator with the following formula:

DELETE TableName;

If you are are using MySQL, use the DELETE FROM operator with the following formula:

DELETE FROM TableName;

When this statement is executed, all records from the TableName factor would be removed from the table. Be careful when doing this: remember that you would not get a warning.

Removing a Record

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, 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)

Practical Learning: Removing Records

To remove a particular record from the table, execute the following statement:
DELETE FROM Videos WHERE VideoTitle = 'The Lady Killers';
To clear the table of all videos, execute the following statement:
DELETE FROM Videos;
To delete the database used in this lesson, execute the following statement:
DROP DATABASE VideoCollection;
To end the lesson, type exit and press Enter


Previous	Copyright © 2005-2016, FunctionX	Next