Using the Update Statement

Update and Delete Statements

As with the SELECT statement, which is the other statement used to query data in SQL, the UPDATE statement is the only statement used to modify data in one or more rows.

Here is the basic syntax:

UPDATE schema.tablename SET columname1=value, columnname2=value... WHERE conditions

Only one table is updated by the UPDATE statement. That is defined directly after the UPDATE keyword.

The SET keyword comes next, followed by a list of column names and values. The data type of each value must match the data type of the table column. Here is a sample statment:

The order of the columns is not important. They may be listed in any order. If the values are not the correct data type, an error will be generated and the statement will fail.

Basic UPDATE statement example

Figure 1: Basic UPDATE Statement Example

Notice that the Query Manager only displays a message indicating how many rows were affected. This is the number of rows that matched the conditions. It does NOT necessarily mean the number of rows that were actually changed. We will explore later how some rows may not be changed.

No error is generated if the UPDATE statement does not affect any rows. For example, the condition in the following statement will not match any rows.

UPDATE Statement With No Matching Rows

Figure 2: UPDATE Statement With No Matching Rows

Importance of WHERE Clause

Almost all UPDATE statements include a WHERE clause. If an UPDATE is executed without a WHERE clause, it will potentially affect ALL rows. That may not be what we want.

When the UPDATE statement is executed, the columns in the statement are changed to the new values, for all rows that meet the WHERE clause conditions. These changes are immediate and permanent!. This is not an UNDO statement in T-SQL.

This underscores the importance of the WHERE clause.

To update a single row, the condition for the WHERE almost always uses the primary key. As in the example above, the condition is

peopleid=7

( It is handy when coding the UPDATE statements to place the conditions on a separate line by themselves, as in the example above. IF the WHERE clause and conditions are placed on the same line, and the user accidently only highlights the main statement - without the WHERE clause - all the records in the table would be updated incorrectly.)

Updating Multiple Records

It is possible to update multiple records, based on the conditions in the WHERE clause. Here is another sample statement with a different WHERE clause:

UPDATE Statement for Multiple Rows

Figure 3: UPDATE Statement for Multiple Rows

In this case, the condition stipluates all rows where the peopleid column equals 7 or greater. There are two rows in the sample table that meet this condition. Both rows will be changed.

Notice that in this case that there are two T-SQL statements highlighted. They were executed consecutively when the F5 key was pressed. So, in this example, the message explaining how many rows were affected is overwritten by the results of the SELECT statement query.

Rows Affected and Actual Updates

As noted previously, the database will report the number of records affected by the UPDATE statement. This does not necessarily mean that all the rows were actually changed. How can that happen? Consider the following statement:

UPDATE Statement Affecting Fewer Rows Than Reported

Figure 4: UPDATE Statement Affecting Fewer Rows Than Reported

In this case, the statement does not include a WHERE clause, meaning all rows will be included. But the REPLACE function will only change the fullname column when the value includes the string "Smith". So, while all 8 rows will be processed, changes will only occur for two rows.

Removing Rows

There are three ways that rows are removed from a table. One of those methods is the DELETE statement. Here is the syntax:

DELETE schema.tablename WHERE condition(s)

As with the UPDATE statement, DELETE will almost always include a WHERE clause. And as with UPDATE, the primary key will often be used when needing to remove a single row.

DELETE Statement Example

Figure 5: DELETE Statement Example

As seen in the output results, one record was removed and the record with peopleid=7 no longer exists in the table. Once deleted, a record cannot be recoverd. There is NOT and UNDO statement.

If the WHERE clause conditions match multiple rows, all those rows would be removed.

When rows are removed from a table that includes an IDENTITY auto-numbering key, the removed values will not appear again. For example, once a record with peopleid=7 is removed, no new records will assign 7 to the peopleid column.

( When including DELETE statements in a script with other statements, comment out the DELETE statements, then execute them by executing the statement after the comment marks. This prevents accidently removing records if multiple script lines are executed.)

Other Ways to Remove Records From a Table

The DROP TABLE removes a table from the database; so it also removed all rows which belonged to the table.

The TRUNCATE TABLE statement will remove all rows from a table without removing the table. TRUNCATE TABLE leaves the table structure in place. Of special note, it also resets the IDENTITY counter. In other words, if the original IDENTITY clause started the primary key values at 1001, then as new records are added to the table after TRUNCATE TABLE, they will again start with 1001.

TRUNCATE TABLE Statement Example

Figure 6: TRUNCATE TABLE Statement Example

As with other statements, TRUNCATE TABLE takes affect immediately and is not reversible. All records are removed immediately. There is no option to use a WHERE clause.