Collections:
Using Values from Other Tables in UPDATE Statements in SQL Server
How To Use Values from Other Tables in UPDATE Statements in SQL Server?
✍: FYIcenter.com
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".
⇒ UPDATE Subquery Returning No Rows in SQL Server
⇐ Importance of Column Order in the SET Clause in Update Statements in SQL Server
2016-11-02, 1484🔥, 0💬
Popular Posts:
How To Get the Definition of a User Defined Function Back in SQL Server Transact-SQL? If you want ge...
How To Convert Binary Strings into Integers in SQL Server Transact-SQL? Binary strings and integers ...
How to continue to the next iteration of a WHILE loop in SQL Server Transact-SQL? How to use CONTINU...
How To Provide Default Values to Function Parameters in SQL Server Transact-SQL? If you add a parame...
How To Use DATEADD() Function in SQL Server Transact-SQL? DATEADD() is a very useful function for ma...