Basic Oracle Concepts and Programming Question:

Download Job Interview Questions and Answers PDF

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

Oracle Database Interview Question
Oracle Database Interview Question

Answer:

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 ggl_links SET (notes, created) =
(SELECT last_name, hire_date FROM employees
WHERE employee_id = id)
WHERE id < 110;
3 rows updated.

SELECT * FROM ggl_links WHERE id < 110;
<pre> ID URL NOTES COUNTS CREATED
---- ------------------------ --------- ------- ---------
101 http://www.globalguideline.com Kochhar 999 21-SEP-89
102 http://www.globalguideline.com De Haan 0 13-JAN-93
103 http://www.globalguideline.com Hunold NULL 03-JAN-90</pre>
This statement updated 3 rows with values from the employees table.

Download Oracle Database Interview Questions And Answers PDF

Previous QuestionNext Question
How To Use Existing Values in UPDATE Statements using Oracle?What Happens If the UPDATE Subquery Returns Multiple Rows?