Oracle 10g update with subselect

They merely demonstrate the use of the WITH clause. Using the SCOTT schema, for each employee we want to know how many other people are in their department. Using an inline view we might do the following. What if we also want to pull back each employees manager name and the number of people in the managers department? Using the inline view it now looks like this.

So we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the WITH clause, making the query much easier to read. It only takes a minute to sign up. Connect and share knowledge within a single location that is structured and easy to search. The documentation for the Update statement allows this syntax even in 10g, so perhaps this is a bug.

I looked for one on My Oracle Support, but couldn't find one. Perhaps you should open a SR with Oracle to see what they say. As a workaround if you re-arrange the statement to have the literal first it will work on both versions. I don't see why either should be necessary. Updating a column to the results of a compound expression that contains a scalar subquery expression added to a value is supported by the syntax and should work.

Similar behavior found here also with no resolution. I think you may just be missing a semi-colon after the update statement. Are you running the exact same code on 11g as 10g? Sign up to join this community. The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? The following statement returns the product name, list price, and the average list prices of products according to their categories:.

Oracle evaluates the subquery for each row selected by the outer query. This subquery is called a correlated subquery which we will cover in detail in the next tutorial.

The subqueries that use comparison operators e.. For example, the following query finds products whose list price is greater than the average list price. The subquery that uses the IN operator often returns a list of zero or more values.

After the subquery returns the result set, the outer query makes uses of them.