How to use old values to define new values in UPDATE statements in SQL Server?

If a row matches the WHERE clause in a UPDATE statement, existing values in this row can be used in expressions to provide new values in the SET clause. Existing values are represented by column names in the expressions. The tutorial exercise below shows you a good example:

SELECT * FROM fyi_links WHERE id >= 500
GO
id      url                     notes   counts  created
601     moc.retneciyf.ved       Wrong   9       2006-04-30
602     moc.retneciyf.abd       Wrong   9       2007-05-21
603     moc.retneciyf.aqs       Wrong   9       2007-05-23

UPDATE fyi_links SET id = id+200, counts = id*2 
   WHERE id >= 500
GO
(3 row(s) affected)

SELECT * FROM fyi_links WHERE id >= 500
GO
id      url                     notes   counts  created
801     moc.retneciyf.ved       Wrong   1202    2006-04-30
802     moc.retneciyf.abd       Wrong   1204    2007-05-19
803     moc.retneciyf.aqs       Wrong   1206    2007-05-19

This statement increased values in the id column by 200. It also updated the counts column with the newly increased id value.

⇒ Importance of Column Order in the SET Clause in Update Statements in SQL Server

⇐ Updating Multiple Rows with One UPDATE Statement in SQL Server

⇑ Using INSERT, UPDATE and DELETE Statements in SQL Server

⇑⇑ SQL Server Database Tutorials