TSQL: Updating Data

Syntax:

UPDATE <object> SET column_name={expression | DEFAULT | NULL};
UPDATE <object> SET column_name={expression | DEFAULT | NULL}
WHERE <search_condition>

Notes:

• Updates that don't explicitly reference a column with a default will not invoke (or re-invoke) the default value. Using the DEFAULT keyword causes the default to be applied during the update.
• Updates NULL for columns that don't actually have a default constraint.
• Subquery enclosed in parentheses returning a single value is permitted.

Samples:

update p
set EmailPromotion = 2
from Person p
     inner join BusinessEntityAddress  b on b.EntityID = p.EntityID
where city = 'Vegas'

update EmailAddress
set EmailAddress = 'jenny.juanillas@gmail.com', modifieddate = default
where EntityID = 123

update ProductInventory
set Quantity *= 2
where ProductID = 123


Compound Assignment Operators

Modifies based on existing column value

• += add and assign
• -= subtract and assign
• *= multiply and assign
• /= divide and assign
• %= modulo and assign
• &= bitwise AND and assign
• ^= bitwise XOR and assign
• |= bitwise OR and assign

Updating Views

• View must be updatable. Must reference exactly one base table in the view definition
• UPDATE must reference columns from a single base table
• Column references must be to data and not to computed columns or aggregate functions
• No TOP allowed in the view definition
• Columns modified can't be part of GROUP BY, HAVING, or DISTINCT

Use INSTEAD OF triggers to modify data.
(Insert and Delete are also allowed against views).