Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
MS SQL Server Interview Question:
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
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.