Oracle - Use Values from Other Tables in UPDATE in Oracle

Use Values from Other Tables in UPDATE in Oracle

Q

How To Use Values from Other Tables in UPDATE Statements in Oracle?

✍: FYIcenter.com

A

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 exercise below shows a good example:

UPDATE fyi_links SET (notes, created) = 
  (SELECT last_name, hire_date FROM employees
    WHERE employee_id = id)
  WHERE id < 110;
3 rows updated.

SELECT * FROM fyi_links WHERE id < 110;
  ID URL                      NOTES      COUNTS CREATED
---- ------------------------ --------- ------- ---------
 101 http://dev.fyicenter.com Kochhar       999 21-SEP-89
 102 http://dba.fyicenter.com De Haan         0 13-JAN-93
 103 http://sqa.fyicenter.com Hunold    NULL    03-JAN-90

This statement updated 3 rows with values from the employees table.

⇒Understanding SQL DML Statements for Oracle

⇒⇒Oracle Database Tutorials

2020-01-21, 568👍, 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, 580👍, 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, 569👍, 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, 555👍, 0💬

Error: Single-Row Subquery Returns More Than One Row in Oracle
What Happens If the UPDATE Subquery Returns Multiple Rows in Oracle? 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... 2020-01-21, 493👍, 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.