SQL (Structured Query Language) is a standard interactive and programming language for getting information from and updating a relational database. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, and Ingres. The standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database. For additional help and reference material

SQL Tutorial and Tips

      Select | Insert | Update | Delete

      Open Cursor | Fetch

      Close Cursor | Union

        Structured Query Language
UPDATE

Syntax

UPDATE {table_name | view_name}
SET [{table_name | view_name}]
{column_list
| variable_list | variable_and_column_list}
[, {column_list2
| variable_list2
| variable_and_column_list2}
... [, {column_listN
| variable_listN
| variable_and_column_listN}]]
[WHERE clause]

where
table_name | view_name =
[[database_name.]owner.]{table_name | view_name}

Specifies the name of the table or view used in the UPDATE statement. If the table or view is not in the current database, use a fully qualified table_name or view_name (database_name.owner.object_name).

SET

Is a required keyword used to introduce the list of column or variable clauses to be updated. When more than one column name and value pair are listed, separate the names with commas.

column_list =
column_name = {expression | DEFAULT | NULL}

variable_list =
variable_name = {expression | NULL}

column_name

Specifies a column from the table (table_name) or view (view_name). expression Is a column_name, constant, function (aggregate functions are not allowed), or any combination of column_names, constants, and functions connected by an operator(s), or a subquery. For details, see the Expressions topic.



DEFAULT

Inserts the default value for that column. For a column defined with the timestamp datatype, the next value will be inserted. If a default does not exist for the column and the column allows NULLs, NULL will be inserted.

DEFAULT is not valid for an identity column; columns with the IDENTITY property should not be explicitly updated. For more information, see the CREATE TABLE statement.

variable_and_column_list =
variable_name = column_name = {expression | NULL}
Sets local variables as well as columns. This syntax provides the same functionality as an UPDATE statement followed by a SELECT statement in an explicitly defined transaction. Because the combination of selecting and updating is performed during a single UPDATE statement, the transaction is implicit and locks are held for a shorter period of time. For samplesyntax, see "Setting Variables and Columns," later in this topic.

WHERE clause =
WHERE {search_conditions | CURRENT OF cursor_name}

Is used to perform a searched update (using search_conditions) or a positioned update (using CURRENT OF cursor_name). When no WHERE clause is given in the UPDATE statement, all rows in the table are modified.

search_conditions

Specifies the criteria for a searched update. A search_condition can include an expression, a subquery, a constant, and so on. For details on valid search_conditions, see the Search Conditions topic.

The IDENTITYCOL keyword can be used in the place of a column_name that has the IDENTITY property. For more information, see the CREATE TABLE statement.

CURRENT OF cursor_name

Specifies the criteria for a positioned update against the current row within the specified cursor_name. Cursor operations affect only the single row on which the cursor is positioned. For details about cursors, see the Cursors topic.



Remarks:

Use the UPDATE statement to change single rows, groups of rows, or all rows in a table. UPDATE specifies which row(s) to change and provides the new data. When updating rows, these rules apply:

- Updating a column with an empty string (' ') into a varchar or text column inserts a single space. All char columns are right-padded to the defined length. All trailing spaces are removed from data inserted into varchar columns, except in strings that contain only spaces. These strings are truncated to a single space.

- If a column being updated violates a constraint or rule, or if it is the wrong datatype, the update does not take place and an error message is returned. Constraints are defined with the CREATE TABLE or ALTER TABLE statement. Rules are created with the CREATE RULE statement and bound with the sp_bindrule system stored procedure.

- Modifying a text column with UPDATE initializes it, assigns a valid text pointer to it, and allocates at least one 2K data page (even if updating the column with NULL). For details about updating text or image fields, see the Text and Image Manipulation topic.

Note:

The UPDATE statement is logged; if you are replacing or modifying large blocks of text or image data, use the WRITETEXT or UPDATETEXT statement instead of the UPDATE statement. The WRITETEXT and UPDATETEXT statements (by default) are not logged. For details, see the Text and Image Manipulation topic.

An UPDATE statement must follow the rules for a batch. For details, see the Batches topic.

Examples: See Examples A.     B.     C.
YouTube - SQL Update Statement Examples
Full Screen Full Screen