To visually modify one or more records on a table, first open it 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.
UPDATE TableName SET ColumnName Operator Expression
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 VideoCollection2; GO USE VideoCollection2; GO CREATE TABLE Videos ( 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 VideoCollection2; 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:
To update one column on all records, use the above formula. To update more than one column, use one SET operator, assign a value to each column but separate them with commas.
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 N'The Lady Killers' video of the following table. It could be that the value is wrong, such as the Director of the the N'The Distinguished Gentleman' video of this table:
To visually 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 programmatically edit a record, we mentioned earlier that you must use the WHERE keyword to create a Boolean expression that specify what column to use and what value must bee application. In reality, the WHERE expression is preceded by an UPDATE statement. This is done using the following formula:
UPDATE Videos SET Director = N'Jonathan Lynn' WHERE VideoTitle = N'The Distinguished Gentleman';
Another solution is to use a combination of columns so that the combination uniquely identifies or isolates the record. Here is an example:
UPDATE Videos SET YearReleased = 1996 WHERE (VideoTitle = N'Ghosts of Mississippi') AND (Director = N'Rob Reiner'); GO
Consider the following table named Employees:
USE Exercise; GO 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:
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 statement, 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(s) of the column(s) you want to show.
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 in the Query Designer, and:
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 [FROM] TableName;
The FROM keyword is optional. 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. Here is an example:
DELETE Employees; GO
This statement asks the database engine to clear the Employees table of all records. Alternatively, you can precede the name of the table with FROM. Here is an example used to clear the table of all videos:
DELETE FROM Videos; GO
Instead of removing all records, to delete only the first n of a table, use the following formula:
DELETE TOP (Number) TableName;
In the parentheses, enter the desired number of records. When the statement executes, the first n records of the table would be deleted. Here is an example:
DELETE TOP (2) Employees; GO
This statement asks the database engine to delete the first two records of the Employees table.
If you find out that a record is not necessary, not anymore, or is misplaced, you can remove it from a table.
To visually remove a record from a table, open the table in Table view, right-click the gray box of the record 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 SQL, to delete a record, use the DELETE FROM statement associated with 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';
Consider the following table:
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 DELETE expression acts on all records of a table. As an alternative, you can ask the database engine to consider only the first n records of a table. The formula to do this is:
DELETE TOP (Number) FROM TableName WHERE Condition(s)
In the parentheses after TOP, enter the desired number of records. When the statement executes, the WHERE condition would be applied on only the first Number of records. Any record that falls in that condition would be deleted. Here is an example:
DELETE TOP (4) FROM Employees WHERE HourlySalary < 12.50; GO
This code asks the database engine to delete any record in the first four records of the Employees table if that hourly salary of the employee in less than 12.50.
If you don't want to specify a fixed number of records, you can use a percentage instead. The formula to follow is:
DELETE TOP (Number) PERCENT FROM TableName WHERE Condition(s)
In the parentheses, enter a number between 0.00 and 100.00 included. The number of records to consider is based on the total number of the records using a percentage. Here is an example:
DELETE TOP (40) PERCENT FROM Employees WHERE HourlySalary < 12.50; GO
This code delete any record whose salary is less than 12.50 but the record must be among the first 40% of the records.
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