SQL 2005+

Basic SQL Server Data Manipulation

by Richard Carr, published at http://www.blackwasp.co.uk/SQLBasicManipulation.aspx

The seventh part of the SQL Server Programming Fundamentals tutorial uses the tutorial database created in the previous articles to take a first look at data manipulation. In this article we will create, update and delete information in SQL Server tables.

Updating Data

Once you have information in your database you can modify it using the UPDATE statement. This command requires that you specify the table to be modified followed by a comma-separated list of the changes that you wish to make to individual columns. Columns that are to remain unchanged are not included in the list. The basic syntax of the statement is as follows:

UPDATE table-name
SET column1 = value1,
    column2 = value2,
    ...,
    columnN = valueN

Using this syntax, every row in the specified table is updated. For example, to increase the hourly rate of every engineer by one pound, execute the following command. Note the use of the column name in a calculation to the right of the assignment operator (=). The new value for the column can be a literal value or can be calculated.

UPDATE Engineers
SET HourlyRate = HourlyRate + 1

The WHERE Clause

Although the above syntax for updating information is useful, more often you will want to update a single item or a set of rows that meet a given criteria. This is achieved by adding a WHERE clause to the statement. The WHERE clause contains a condition that is checked for every row in the table. If the condition is met, the row is updated. If not, the row remains unchanged.

To demonstrate the WHERE clause, execute the update statement below. This statement increases the hourly rate for all engineers that currently have a rate of £29.50 or greater. If the engineer is paid less than £29.50, their rate remains the same.

UPDATE Engineers
SET HourlyRate = HourlyRate + 0.25
WHERE HourlyRate >= 29.50

The WHERE clause will be considered in greater detail in the next article in this series.

Deleting Data

The last T-SQL data manipulation command to consider is DELETE. This command simply removes rows from a table. As with UPDATE, a DELETE statement can be executed without a WHERE clause to empty a table, or with a WHERE clause to delete rows that match specified criteria.

To demonstrate, we will delete all engineers with an hourly rate over £29.50:

DELETE FROM Engineers WHERE HourlyRate > 29.50

NB: Note the use of the "FROM" keyword. This is optional but some SQL developers prefer to include it.