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, 1441🔥, 0💬
Popular Posts:
What Is an Oracle Instance in Oracle? Every running Oracle database is associated with an Oracle ins...
How to execute statements under given conditions in SQL Server Transact-SQL? How to use IF ... ELSE ...
How To Create a Stored Program Unit in Oracle? If you want to create a stored program unit, you can ...
What Happens to Your Transactions When ERROR 1205 Occurred in MySQL? If your transaction receives th...
How To Present a Past Time in Hours, Minutes and Seconds in MySQL? If you want show an article was p...