How To Use Values from Other Tables in UPDATE Statements in MS SQL Server?
Submitted by: AdministratorIf 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 ggl_rates (id INTEGER,
comment VARCHAR(16))
Go
-- Insert some rows in the new table
INSERT INTO ggl_rates VALUES (101, 'The best')
Go
INSERT INTO ggl_rates VALUES (102, 'Well done')
GO
INSERT INTO ggl_rates VALUES (103, 'Thumbs up')
Go
-- Update ggl_links with values from ggl_rates
UPDATE ggl_links SET notes = (
SELECT comment FROM ggl_rates
WHERE ggl_rates.id = ggl_links.id
)
WHERE id > 0 AND id < 110
GO
(3 row(s) affected)
-- View the updated values
SELECT * FROM ggl_links
WHERE id > 0 AND id < 110
GO
id url notes counts created
101 www.globalguideline.com The best 999 2006-04-30
102 www.globalguideline.com/html Well done 0 2007-05-19
103 www.globalguideline.com/sql Thumbs up NULL 2007-05-19
Note that if column names are confusing between the inner table and the outer table, you need to pref
Submitted by: Administrator
-- Create another table
CREATE TABLE ggl_rates (id INTEGER,
comment VARCHAR(16))
Go
-- Insert some rows in the new table
INSERT INTO ggl_rates VALUES (101, 'The best')
Go
INSERT INTO ggl_rates VALUES (102, 'Well done')
GO
INSERT INTO ggl_rates VALUES (103, 'Thumbs up')
Go
-- Update ggl_links with values from ggl_rates
UPDATE ggl_links SET notes = (
SELECT comment FROM ggl_rates
WHERE ggl_rates.id = ggl_links.id
)
WHERE id > 0 AND id < 110
GO
(3 row(s) affected)
-- View the updated values
SELECT * FROM ggl_links
WHERE id > 0 AND id < 110
GO
id url notes counts created
101 www.globalguideline.com The best 999 2006-04-30
102 www.globalguideline.com/html Well done 0 2007-05-19
103 www.globalguideline.com/sql Thumbs up NULL 2007-05-19
Note that if column names are confusing between the inner table and the outer table, you need to pref
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | What Happens If NULL Values Are Involved in Bitwise Operations? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How to download Microsoft SQL Server 2005 Express Edition? |
☺ | Can You Roll Back the DDL Statement in a Trigger? |
☺ | What Is a Subquery in a SELECT Query Statement in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |