What Happens If the UPDATE Subquery Returns Multiple Rows in MS SQL Server?
Submitted by: AdministratorIf a subquery is used in a UPDATE statement, it must return exactly one row for each row in the update table that matches the WHERE clause. If it returns multiple rows, SQL Server will give you an error message. To test this out, you can try the following tutorial exercise:
-- insert two rows to ggl_rates
INSERT INTO ggl_rates VALUES (0, 'Number 1')
GO
INSERT INTO ggl_rates VALUES (0, 'Number 2')
GO
-- make sure there are 2 match rows
SELECT * FROM ggl_rates WHERE id = 0
GO
id comment
0 Number 1
0 Number 2
-- update with subquery that returns 2 rows
UPDATE ggl_links SET notes = (
SELECT comment FROM ggl_rates
WHERE ggl_rates.id = ggl_links.id
)
WHERE id = 0
GO
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted
when the subquery follows =, !=, <, <= , >, >= or when
the subquery is used as an expression.
The statement has been terminated.
It is clear that we are using subquery as an expression, and it must return 0 or 1 row. Otherwise, we will get an error.
Submitted by: Administrator
-- insert two rows to ggl_rates
INSERT INTO ggl_rates VALUES (0, 'Number 1')
GO
INSERT INTO ggl_rates VALUES (0, 'Number 2')
GO
-- make sure there are 2 match rows
SELECT * FROM ggl_rates WHERE id = 0
GO
id comment
0 Number 1
0 Number 2
-- update with subquery that returns 2 rows
UPDATE ggl_links SET notes = (
SELECT comment FROM ggl_rates
WHERE ggl_rates.id = ggl_links.id
)
WHERE id = 0
GO
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted
when the subquery follows =, !=, <, <= , >, >= or when
the subquery is used as an expression.
The statement has been terminated.
It is clear that we are using subquery as an expression, and it must return 0 or 1 row. Otherwise, we will get an error.
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Use Subqueries with the IN Operators in MS SQL Server? |
☺ | How To Declare and Use Cursor Variables? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How To Configure ODBC DSN with Different Port Numbers? |
☺ | How To Create a Large Table with Random Data for Index Testing 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. |