Collections:
UPDATE Subquery Returning Multiple Rows in SQL Server
What Happens If the UPDATE Subquery Returns Multiple Rows in SQL Server?
✍: FYIcenter.com
If 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 fyi_rates INSERT INTO fyi_rates VALUES (0, 'Number 1') GO INSERT INTO fyi_rates VALUES (0, 'Number 2') GO -- make sure there are 2 match rows SELECT * FROM fyi_rates WHERE id = 0 GO id comment 0 Number 1 0 Number 2 -- update with subquery that returns 2 rows UPDATE fyi_links SET notes = ( SELECT comment FROM fyi_rates WHERE fyi_rates.id = fyi_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.
⇒ Deleting an Existing Row with DELETE Statements in SQL Server
⇐ UPDATE Subquery Returning No Rows in SQL Server
2016-11-02, 3307🔥, 0💬
Popular Posts:
How To Get the Definition of a Stored Procedure Back in SQL Server Transact-SQL? If you want get the...
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...
How To Download Oracle Database 10g XE in Oracle? If you want to download a copy of Oracle Database ...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...
How To Select All Columns of All Rows from a Table with a SELECT statement in SQL Server? The simple...