Home

	Expand Search

Transact-SQL User's Guide

Chapter 8 Adding, Changing, and Deleting Data

Changing existing data

Changing existing data

Use the update command to change single rows, groups of rows, or all rows in a table. As in all data modification statements, you can change the data in only one table at a time.

update specifies the row or rows you want changed and the new data. The new data can be a constant or an expression that you specify or data pulled from other tables.

If an update statement violates an integrity constraint, the update does not take place and an error message is generated. The update is canceled, for example, if it affects the table's IDENTITY column, or if one of the values being added is the wrong datatype, or if it violates a rule that has been defined for one of the columns or datatypes involved.

Adaptive Server does not prevent you from issuing an update command that updates a single row more than once. However, because of the way that update is processed, updates from a single statement do not accumulate. That is, if an update statement modifies the same row twice, the second update is not based on the new values from the first update but on the original values. The results are unpredictable, since they depend on the order of processing.

See Chapter 9, "Views: Limiting Access to Data," for restrictions on updating views.

The update command is logged. If you are changing large blocks of text or image data, try using the writetext command, which is not logged. Also, you are limited to approximately 125K per update statement. See the discussion of writetext in "Changing text and image data".

update syntax

A simplified version of the update syntax for updating specified rows with an expression is:

update table_name 
     set column_name = expression 
     where search_conditions

For example, if Reginald Blotchet-Halls decides to change his name, here is how to change his row in the authors table:

update authors 
set au_lname = "Health", au_fname = "Goodbody" 
where au_lname = "Blotchet-Halls"

This statement updates a table based on data from another table:

update table_name 
     set column_name = expression 
          from table_name 
          where search_conditions 

You can set variables in an update statement with:

update table_name 
     set variable_name = expression 
          where search_conditions 

The full syntax for update is:

update [[database.]owner.]{table_name | view_name}
     set [[[database.]owner.]{table_name. | view_name.}] 
          column_name1 = 
          {expression1 | null | (select_statement)} |
          variable_name1 = 
          {expression1 | null | (select_statement)} 
          [, column_name2 = {expression2 | null |
          (select_statement)}]... | 
               variable_name2 = {expression1 | null |                  (select_statement)} 
     [from [[database.]owner.] {table_name | view_name} 
          [, [[database.]owner.] {table_name | 
          view_name}]]... 
     [where search_conditions] 

Using the set clause with update

The set clause specifies the columns and the changed values. The where clause determines which row or rows are to be updated. If you do not have a where clause, the specified columns of all the rows are updated with the values given in the set clause.

Before trying the examples in this section, make sure you know how to reinstall the pubs2 database. See the installation and configuration guide for your platform for instructions on installing the pubs2 database.

For example, if all the publishing houses in the publishers table move their head offices to Atlanta, Georgia, this is how you update the table:

update publishers 
set city = "Atlanta", state = "GA"

In the same way, you can change the names of all the publishers to NULL with:

update publishers 
set pub_name = null

You can also use computed column values in an update. To double all the prices in the titles table, use:

update titles 
set price = price * 2

Since there is no where clause, the change in prices is applied to every row in the table.

You can assign variables in the set clause of an update statement, in the same way you can assign them in a select statement. Using variables with update reduces lock contention and CPU consumption that can occur when extra select statements are used in conjunction with update.

This example uses a declared variable to update the titles table:

declare @price money
select @price = 0
update titles
    set total_sales = total_sales + 1,
    @price = price
    where title_id = "BU1032"
select @price, total_sales
    from titles
    where title_id = "BU1032"

                          total_sales
 ------------------------ -----------
                    19.99        4096
 
(1 row affected)

For details on assigning variables in an update statement, see the Reference Manual. For more information on declared variables, see "Local variables".

Using the where clause with update

The where clause specifies which rows are to be updated. For example, in the unlikely event that Northern California is renamed Pacifica (abbreviated PC) and the people of Oakland vote to change the name of their city to Big Bad Bay City, here is how you can update the authors table for all former Oakland residents whose addresses are now out of date:

update authors 
set state = "PC", city = "Big Bad Bay City" 
where state = "CA" and city = "Oakland" 

You need to write another statement to change the name of the state for residents of other cities in Northern California.

Using the from clause with update

Use the from clause to pull data from one or more tables into the table you are updating.

For example, earlier in this chapter, an example was given for inserting some new rows into the titleauthor table for authors without titles, filling in the au_id column, and using dummy or null values for the other columns. When one of these authors, Dirk Stringer, writes a book, The Psychology of Computer Cooking , a title identification number is assigned to his book in the titles table. You can modify his row in the titleauthor table by adding a title identification number for him:

update titleauthor
set title_id = titles.title_id
from titleauthor, titles, authors
    where titles.title =
    "The Psychology of Computer Cooking"
    and authors.au_id = titleauthor.au_id
    and au_lname = "Stringer"

Note that an update without the au_id join changes all the title_ids in the titleauthor table so that they are the same as The Psychology of Computer Cooking 's identification number. If two tables are identical in structure except that one has NULL fields and some null values and the other has NOT NULL fields, it is impossible to insert the data from the NULL table into the NOT NULL table with a select. In other words, a field that does not allow NULL cannot be updated by selecting from a field that does, if any of the data is NULL.

As an alternative to the from clause in the update statement, you can use a subquery, which is ANSI-compliant.

Updates with joins

The following example joins columns from the titles and publishers tables, doubling the price of all books published in California:

update titles 
  set price = price * 2 
  from titles, publishers 
  where titles.pub_id = publishers.pub_id 
  and publishers.state = "CA"

Although a join can usually be done with up to 16 tables, if a join is part of an update or delete statement, the query can only refer to 15 tables.

Updating IDENTITY columns

You can use the syb_identity keyword, qualified by the table name, where necessary, to update an IDENTITY column. For example, this update statement finds the row in which the IDENTITY column equals 1 and changes the name of the store to "Barney's":

update stores_cal
set stor_name = "Barney's"
where syb_identity = 1