SQLConfiguration SQLConfiguration

CRUD Statements  «Prev  Next»
Lesson 5Using literal values
ObjectiveDescribe how to use literal values in your INSERT, UPDATE, and DELETE statements.
Now that you know the syntax of inserting, updating, and deleting data into your SQL Server tables, let’s put this knowledge to use. The first thing that you need to know is how to use literal values. A literal value is specified in the query statement. Contrasting a literal value is one that is “looked-up” by SQL Server in another table, column, or database.
Literal value: A value that is actually specified in Transact-SQL code and not stored in a variable or table. Examples of literal values include free, tmann@thesqlsolution.com, 72, and 1283. Literal values that store character data are called string literals and are always enclosed in quotations.

INSERT statement

A client has asked you to update some of its employee records. A new employee, Anthony Mann, has just started and needs to be entered into the employee table.
INSERT INTO Employees(FirstName, LastName)  
VALUES ('Anthony','Mann')

In the above statement, the literal value 'Anthony' is inserted into the FirstName column of the Employees table, and the literal value 'Mann' is inserted into the LastName column of the Employees table.

UPDATE statement

Perhaps Anthony Mann is already recorded in the database, but his first and last names are spelled incorrectly. The following UPDATE statement could be used to change ‘Tony Man’ to ‘Anthony Mann.’

UPDATE Employees
SET FirstName = 'Anthony',
LastName = 'Mann'
WHERE EmployeeID = 101

Note that only the rows that have an EmployeeID of 101 will be updated. This prevents data from other employees with the same name from being affected. If EmployeeID is the primary key or has a unique key constraint, it is guaranteed not to have a duplicate value, so only one row will be updated.

DELETE statement

If Anthony Mann no longer works for your client, he can be deleted from the Employees table with the following command.
WHERE EmployeeID = 101
Because there are no column names specified for a DELETE statement, literal values are only valid in the WHERE clause.
In the next lesson, you will learn more about different approaches to using literal values.