Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Using Values from Other Tables in UPDATE Statements

By: FYIcenter.com

(Continued from previous topic...)

How To Use Values from Other Tables in UPDATE Statements?

If you want to update values in one table with values from another table, you can use a subquery as an expression in the SET clause. The subquery should return only one row for each row in the update table that matches the WHERE clause. The tutorial exercise below shows you a good example:

-- Create another table
CREATE TABLE fyi_rates (id INTEGER, 
   comment VARCHAR(16))
Go

-- Insert some rows in the new table
INSERT INTO fyi_rates VALUES (101, 'The best')
Go
INSERT INTO fyi_rates VALUES (102, 'Well done')
GO
INSERT INTO fyi_rates VALUES (103, 'Thumbs up')
Go

-- Update fyi_links with values from fyi_rates
UPDATE fyi_links SET notes = (
      SELECT comment FROM fyi_rates 
      WHERE fyi_rates.id = fyi_links.id
   )
   WHERE id > 0 AND id < 110
GO
(3 row(s) affected)

-- View the updated values
SELECT * FROM fyi_links 
   WHERE id > 0 AND id < 110
GO
id    url                  notes        counts  created
101   dev.fyicenter.com    The best     999     2006-04-30
102   dba.fyicenter.com    Well done    0       2007-05-19
103   sqa.fyicenter.com    Thumbs up    NULL    2007-05-19

Note that if column names are confusing between the inner table and the outer table, you need to prefix column names with table names, like "fyi_rates.id = fyi_links.id".

(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?