SQL Update

UPDATE
Definition
Change the data in a table or view.

 UPDATE object SET {column name = {expression | DEFAULT | NULL} | @ variable = expression | @ variable = column = expression} [, ... n] [FROM {} [, ... n]] [WHERE {   }]

Description of the clauses

• SET: Specifies the list of variable names or column to be updated.
• Name Column: A column containing the data will change. Must exist in the table or view. Identity columns or computed columns can not be updated.
• Expression: is a variable, literal value, expression, or a subselect statement in parentheses that returns a single value. The value returned by expression replaces the existing value in the column or variable.
• DEFAULT: Specifies the default value defined for the column is to replace the existing value in that column. This can also be used to change the column to null if it has no default and is set to accept null values.
• FROM: Specifies to use a source table, view or derived table to provide the criteria for the update operation. If the object being updated is indicated in the FROM clause and there is only one reference to the object in it, you can specify whether or not an object alias. If the object being updated appears more than once in the FROM clause, a single reference to the object must not specify a table alias. All other references to the object of the clause must include an object alias.
• WHERE: specifies conditions that limit the rows that are updated. Its use is important because if not all the records of the table or view receiving the amendment.

Examples
a. Updates all records in the table SalesPerson.

 UPDATE SET Sales.SalesPerson Bonus = 6000, CommissionPct = .10, SalesQuota = NULL

b. Updates only the records whose name begins with Road-250 and are red. The change shows how to use calculated values.

 UPDATE SET ListPrice = ListPrice Production.Product * 2 WHERE Name LIKE N'Road-250% 'AND color = N'Red'

c. Modify column of the table SalesYTD SalesPerson to reflect the most recent sales recorded in Table SalesOrderHeader.

 SalesYTD Sales.SalesPerson UPDATE SET = + SubTotal FROM Sales.SalesPerson SalesYTD AS sp INNER JOIN AS so ON Sales.SalesOrderHeader sp.SalesPersonID = so.SalesPersonID
 AND so.OrderDate = (SELECT MAX (OrderDate) FROM WHERE Sales.SalesOrderHeader SalesPersonID = sp.SalesPersonID)