Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - UPDATE Subquery Returning Multiple Rows

By: FYIcenter.com

(Continued from previous topic...)

What Happens If the UPDATE Subquery Returns Multiple Rows?

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.

(Continued on next topic...)

  1. What Are DML (Data Manipulation Language) Statements?
  2. How To Create a Testing Table with Test Data?
  3. How To Insert a New Row into a Table with "INSERT INTO" Statements?
  4. How To Use Column Default Values in INSERT Statements?
  5. How to provide column names in INSERT Statements?
  6. What Happens If You Insert a Duplicate Key for the Primary Key Column?
  7. How To Insert Multiple Rows with One INSERT Statement?
  8. How To Update Values in a Table with UPDATE Statements?
  9. How To Update Multiple Rows with One UPDATE Statement?
  10. How to use old values to define new values in UPDATE statements?
  11. Is the Order of Columns in the SET Clause Important?
  12. How To Use Values from Other Tables in UPDATE Statements?
  13. What Happens If the UPDATE Subquery Returns No Rows?
  14. What Happens If the UPDATE Subquery Returns Multiple Rows?
  15. How To Delete an Existing Row with DELETE Statements?
  16. How To Delete Multiple Rows with One DELETE Statement?
  17. How To Delete All Rows with DELETE Statements?
  18. How To Delete All Rows with TRUNCATE TABLE Statement?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...