Error: Single-Row Subquery Returns More Than One Row in Oracle

Q

What Happens If the UPDATE Subquery Returns Multiple Rows in Oracle?

✍: FYIcenter.com

A

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, Oracle server will give you an error message. To test this out, you can try the following tutorial exercise:

UPDATE fyi_links SET (notes, created) = 
  (SELECT last_name, hire_date FROM employees
    WHERE employee_id < id)
  WHERE id < 110;
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

The problem is the criteria in the subquery: "employee_id < id"

⇒ Delete an Existing Row from a Table in Oracle

⇐ Use Values from Other Tables in UPDATE in Oracle

⇑ Understanding SQL DML Statements for Oracle

⇑⇑ Oracle Database Tutorials

2020-01-21, 1785👍, 0💬

Delete Multiple Rows from a Table in Oracle
How To Delete Multiple Rows from a Table in Oracle? You can delete multiple rows from a table in the same way as deleting a single row, except that the WHERE clause will match multiple rows. The tutorial exercise below deletes 3 rows from the fyi_links table: SELECT * FROM fyi_links WHERE id &gt... 2020-01-04, 1554👍, 0💬

Understanding SQL SELECT Query Statements in Oracle
Where to find answers to frequently asked questions on SQL SELECT Query Statements in Oracle? I want to query data from tables. Here is a list of frequently asked questions and their answers compiled by FYIcenter.com DBA team on SQL SELECT Query Statements in Oracle. Clear answers are provided with ... 2020-01-04, 1383👍, 0💬

Delete an Existing Row from a Table in Oracle
How To Delete an Existing Row from a Table in Oracle? If you want to delete an existing row from a table, you can use the DELETE statement with a WHERE clause to identify that row. Here is good sample of DELETE statements: INSERT INTO fyi_links (url, id) VALUES ('http://www.myspace.com', 301); 1 row... 2020-01-04, 1351👍, 0💬

What Is a SELECT Query Statement in Oracle
What Is a SELECT Query Statement in Oracle? The SELECT statement is also called the query statement. It is the most frequently used SQL statement in any database application. A SELECT statement allows you to retrieve data from one or more tables, or views, with different selection criteria, grouping... 2020-01-04, 1331👍, 0💬

Insert Multiple Rows with 1 INSERT Statement in Oracle
How To Insert Multiple Rows with One INSERT Statement in Oracle? If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the target table. The following tutorial exercise gives a good ex... 2020-01-29, 1262👍, 0💬

Use Existing Column Values in SET Clause in Oracle
How To Use Existing Values in UPDATE Statements in Oracle? If a row matches the WHERE clause in a UPDATE statement, existing values in this row can be used in expressions to provide new values in the SET clause. Existing values are represented by columns in the expressions. The tutorial exercise bel... 2020-01-21, 1217👍, 0💬

Use Values from Other Tables in UPDATE in Oracle
How To Use Values from Other Tables in UPDATE Statements in Oracle? If you want to update values in one with values from another table, you can use a subquery 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 exerc... 2020-01-21, 1207👍, 0💬

in Oracle
How To Delete All Rows from a Table in Oracle? If you want to delete all rows from a table, you have two options: Use the DELETE statement with no WHERE clause. Use the TRUNCATE TABLE statement. The TRUNCATE statement is more efficient the DELETE statement. The tutorial exercise shows you a good exa... 2020-01-04, 1196👍, 0💬

Update Values on Multiple Rows in Oracle
How To Update Values on Multiple Rows in Oracle? If the WHERE clause in an UPDATE matches multiple rows, the SET clause will be applied to all matched rows. This rule allows you to update values on multiple rows in a single UPDATE statement. Here is a good example: UPDATE fyi_links SET counts = 9, n... 2020-01-21, 1186👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.