Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Using Old Values to Define New Values in UPDATE Statements

By: FYIcenter.com

(Continued from previous topic...)

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

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.

(Continued on next topic...)

1. What Are DML (Data Manipulation Language) Statements?
2. How To Create a Testing Table with Test Data?
3. How To Insert a New Row into a Table with "INSERT INTO" Statements?
4. How To Use Column Default Values in INSERT Statements?
5. How to provide column names in INSERT Statements?
6. What Happens If You Insert a Duplicate Key for the Primary Key Column?
7. How To Insert Multiple Rows with One INSERT Statement?
8. How To Update Values in a Table with UPDATE Statements?
9. How To Update Multiple Rows with One UPDATE Statement?
10. How to use old values to define new values in UPDATE statements?
11. Is the Order of Columns in the SET Clause Important?
12. How To Use Values from Other Tables in UPDATE Statements?
13. What Happens If the UPDATE Subquery Returns No Rows?
14. What Happens If the UPDATE Subquery Returns Multiple Rows?
15. How To Delete an Existing Row with DELETE Statements?
16. How To Delete Multiple Rows with One DELETE Statement?
17. How To Delete All Rows with DELETE Statements?
18. How To Delete All Rows with TRUNCATE TABLE Statement?