Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

CHAPTER 9 Updating Data > Update by Values and Queries

Update by Values and Queries

Some UPDATE statements use date, numeric, or string literals in the SET subclause. The SET subclause can work with one to all columns in a table, but you should never update a primary surrogate key column. Any update of the externally known identifier column risks compromising the referential integrity of primary and foreign keys.

The generic UPDATE statement prototype with values resetting column values looks like this:

image UPDATE some_table

SET    column_name = 'expression'

[,     column_name = 'expression' [,…]

 WHERE  [NOT]   column_name {{= | <> | > | >= | < | <=} |

                   [NOT] {{IN | EXISTS} | IS NULL}} 'expression'

[{AND | OR} [NOT] comparison_operation] [ . . .];

The target table of an UPDATE statement can be a table or updateable view. An expression can be a numeric or string literal or the return value from a function or subquery. The function or subquery must return only a single row of data that matches the data type of the assignment target. The right operand of the assignment can contain a different data type when its type can be implicitly cast to the column’s data type, or explicitly cast to it with the CAST or proprietary built-in function. In the generic example, a subquery needs to return a single column and row (this type of subquery is a scalar subquery or SQL expression). Ellipses replace multiple listing in the SET and WHERE clauses.


You are currently reading a PREVIEW of this book.


Get instant access to over $1 million worth of books and videos.


Start a Free Trial

  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint