When a row on one table is related to one or more rows in another table This is called a?

Categories:

DML Commands - General

Updates specified rows in the target table with new values.

In this Topic:

Syntax¶

UPDATE 
       SET  =  [ ,  =  , ... ]
        [ FROM  ]
        [ WHERE  ]

Required Parameters¶

Specifies the table to update.

Specifies the name of a column in target_table. Do not include the table name. For example, UPDATE t1 SET t1.col = 1 is invalid.

Specifies the new value to set in col_name.

Optional Parameters¶

Specifies one or more tables to use for selecting rows to update or for setting new values. Note that repeating the target table results in a self-join.

Expression that specifies the rows in the target table to update.

Default: No value [all rows of the target table are updated]

Usage Notes¶

• When a FROM clause contains a JOIN between tables [e.g. t1 and t2], a target row in t1 may join against [i.e. match] more than one row in table t2. When this occurs, the target row is called a multi-joined row. When updating a multi-joined row, the ERROR_ON_NONDETERMINISTIC_UPDATE session parameter controls the outcome of the update:

  • If FALSE [default value], no error is returned and one of the joined rows is used to update the target row; however, the selected joined row is nondeterministic.

  • IF TRUE, an error is returned, including an example of the values of a target row that joins multiple rows.

  To set the parameter:

  ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_UPDATE=TRUE;
  

Examples¶

Perform a standard update using two tables:

UPDATE t1
  SET number_column = t1.number_column + t2.number_column, t1.text_column = 'ASDF'
  FROM t2
  WHERE t1.key_column = t2.t1_key and t1.number_column