DevGuru SQL Syntax: UPDATE

Quick References

Features

Partners

UPDATE Statement Syntax Diagram

Click on the keyword in the simplified syntax diagram below to go directly to that part of the full syntax diagram.

UPDATE ...
    { SET ... }
    [ FROM ... ]
    [ ... JOIN ... ]
    [ WHERE ... ]

Place the cursor on any keyword to see the definition in a pop-up window.
(IE4+, IE5+, beta IE6, N4+)

UPDATE
    {
       [ database. ] [ owner. ] table_name
         [
           WITH ( INDEX ( Index_1, [ index_2, ..., n ] )
                      | FASTFIRSTROW
                      | HOLDLOCK
                      | PAGLOCK
                      | READCOMMITTED
                      | REPEATABLEREAD
                      | ROWLOCK
                      | SERIALIZABLE
                      | TABLOCK
                      | TABLOCKX
                      [ ... n | ..., n ]
                     )
         ]
     |
       OPENQUERY( server, 'query' )
     |
       OPENROWSET( 'provider_name',
                                 { 'datasource';'user_id';'password', | 'provider_string', }
                                 { [ catalog. ] [ schema. ] object | 'query' }
                               )
     |
       view_name
    }

   {
      SET
      {
          column = DEFAULT | NULL | expression
       |
          @variable = expression
       |
          @variable = column = expression
       }
       [ ..., n ]
    }

   [
      FROM
      {
          derived_table
            [ AS table_alias ]
            [ ( column_alias_1, [ column_alias_2, ..., n ] ) ]
        |
          CONTAINSTABLE ( table, column | *, 'search_conditions' )
            [ AS table_alias ]
        |
          FREETEXTTABLE ( table, column | *, 'free_text_string' )
            [ AS table_alias ]
        |
          table_name [ AS table_alias ]
            [
               WITH ( INDEX ( Index_1, [ index_2, ..., n ] )
                          | FASTFIRSTROW
                          | HOLDLOCK
                          | NOLOCK
                          | PAGLOCK
                          | READCOMMITTED
                          | READPAST
                          | READUNCOMMITTED
                          | REPEATABLEREAD
                          | ROWLOCK
                          | SERIALIZABLE
                          | TABLOCK
                          | TABLOCKX
                          | UPDLOCK
                          [ ... n | ..., n ]
                         )
            ]
        |
          view_name [ AS table_alias ]
      }

      [
          INNER JOIN | LEFT [ OUTER ] JOIN | RIGHT [ OUTER ] JOIN
            {
               derived_table [ ON search_conditions ]
             |
               OPENQUERY( server, 'query' ) [ ON search_conditions ]

             |
               OPENROWSET
                               ( 'provider_name',
                                 { 'datasource';'user_id';'password', | 'provider_string', }
                                 { [ catalog. ] [ schema. ] object | 'query' }
                               ) [ ON search_conditions ]
             |
               table_name [ ON search_conditions ]
             |
               view_name [ ON search_conditions ]
            }
            [ ..., n ]
          |
          CROSS JOIN | FULL [ OUTER ] JOIN
            {
               derived_table
             |
               OPENQUERY( server, 'query' )
             |
               OPENROWSET
                               ( 'provider_name',
                                 { 'datasource';'user_id';'password', | 'provider_string', }
                                 { [ catalog. ] [ schema. ] object | 'query' }
                               )
             |
               table_name
             |
               view_name
            }
            [ ..., n ]
       ]
   ]

   [
      {
         WHERE search_conditions
       |
         WHERE CURRENT OF [ GLOBAL ] cursor_name
       |
         WHERE CURRENT OF cursor_variable_name
      }
         [
           OPTION ( FAST number_rows
                          | FORCE ORDER
                          | HASH GROUP
                          | ORDER GROUP
                          | HASH JOIN
                          | LOOP JOIN
                          | MERGE JOIN
                          | KEEP PLAN
                          | MAXDOP
                          | ROBUST PLAN
                          | CONCAT UNION
                          | HASH UNION
                          | MERGE UNION
                          [ ..., n ]
                        )
         ]
   ]

Key to blue and red text in syntax diagram

KEYWORDS	Keywords are denoted with upper case letters. Obey the spelling.
variables	All user-supplied variables are denoted with lower case letters.
..., n	Signifies that there can be more than one value in a comma delimited list. Note that the dots and n are not part of the code and must not appear in the SQL query.
... n	Signifies that there can be more than one value in a blank space delimited list. Note that the dots and n are not part of the code and must not appear in the SQL query.
{ }	Signifies that all, or some portion, of the code elements between the braces are required elements and must appear in the SQL query. Note that these braces are not part of the code and must not appear in the SQL query.
[ ]	Signifies that the code elements between the square brackets can optionally appear in the SQL query, but are not required. Note that these brackets are not part of the code and must not appear in the SQL query.
\|	The or symbol signifies that you may use only one of the code elements or values from the possible choices. Note that the or symbol is not part of the code and must not appear in the SQL query.