Oracle SQL - What Happens If the UPDATE Subquery Returns Multiple Rows

What Happens If the UPDATE Subquery Returns Multiple Rows

Q

What Happens If the UPDATE Subquery Returns Multiple Rows? - Oracle DBA FAQ - Understanding SQL DML Statements

✍: 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"

2007-04-21, 5828👍, 0💬

How To Delete Multiple Rows from a Table
How To Delete Multiple Rows from a Table? - Oracle DBA FAQ - Understanding SQL DML Statements 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... 2007-04-21, 4741👍, 0💬

How To Use Values from Other Tables in UPDATE Statements
How To Use Values from Other Tables in UPDATE Statements? - Oracle DBA FAQ - Understanding SQL DML Statements 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 ma... 2007-04-21, 4431👍, 0💬

How To Delete an Existing Row from a Table
How To Delete an Existing Row from a Table? - Oracle DBA FAQ - Understanding SQL DML Statements 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) VAL... 2007-04-21, 4424👍, 0💬

How To Use Existing Values in UPDATE Statements
How To Use Existing Values in UPDATE Statements? - Oracle DBA FAQ - Understanding SQL DML Statements 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 ... 2007-04-21, 4333👍, 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.