|
INTERVIEW QUESTIONS
DATABASE
MS SQL SERVER
DETAILS
Question: How To Use Values from Other Tables in UPDATE Statements in MS SQL Server?
Answer: 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 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
|
|
|
Category |
MS SQL Server Interview Questions & Answers -
Exam Mode /
Learning Mode
|
Rating |
(0.3) By 7880 users |
Added on |
9/23/2014 |
Views |
69294 |
Rate it! |
|
|
Question:
How To Use Values from Other Tables in UPDATE Statements in MS SQL Server?
Answer:
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 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 Source: CoolInterview.com
If you have the better answer, then send it to us. We will display your answer after the approval.
Rules to Post Answers in CoolInterview.com:-
- There should not be any Spelling Mistakes.
- There should not be any Gramatical Errors.
- Answers must not contain any bad words.
- Answers should not be the repeat of same answer, already approved.
- Answer should be complete in itself.
|
|
Related Questions |
View Answer |
|
Is the Order of Columns in the SET Clause Important in MS SQL Server?
|
View Answer
|
|
How to use old values to define new values in UPDATE statements in MS SQL Server?
|
View Answer
|
|
How To Update Multiple Rows with One UPDATE Statement in MS SQL Server?
|
View Answer
|
|
How To Update Values in a Table with UPDATE Statements in MS SQL Server?
|
View Answer
|
|
How To Insert Multiple Rows with One INSERT Statement in MS SQL Server?
|
View Answer
|
|
What Happens If You Insert a Duplicate Key for the Primary Key Column in MS SQL Server?
|
View Answer
|
|
How to provide column names in INSERT Statements in MS SQL Server?
|
View Answer
|
|
How To Use Column Default Values in INSERT Statements in MS SQL Server?
|
View Answer
|
|
How To Insert a New Row into a Table with "INSERT INTO" Statements in MS SQL Server?
|
View Answer
|
|
How To Create a Testing Table with Test Data in MS SQL Server?
|
View Answer
|
|
What Are DML (Data Manipulation Language) Statements in MS SQL Server?
|
View Answer
|
|
How To Drop an Existing Table with "DROP TABLE" Statements in MS SQL Server?
|
View Answer
|
|
How to rename an existing table with the "sp_rename" stored procedure in MS SQL Server?
|
View Answer
|
|
How to rename an existing column with SQL Server Management Studio?
|
View Answer
|
|
How to rename an existing column with the "sp_rename" stored procedure in MS SQL Server?
|
View Answer
|
|
How To Add a New Column to an Existing Table with "ALTER TABLE ... ADD" in MS SQL Server?
|
View Answer
|
|
How to create new tables with "SELECT ... INTO" statements in MS SQL Server?
|
View Answer
|
|
How To Generate CREATE TABLE Script on an Existing Table in MS SQL Server?
|
View Answer
|
|
How To Get a List of Columns using the "sp_help" Stored Procedure in MS SQL Server?
|
View Answer
|
|
How To Get a List of Table Columns using the "sp_columns" Stored Procedure in MS SQL Server?
|
View Answer
|