Re: Updating a table with an array returned from a select

doom_at_kzsu.stanford.edu (Joe Brenner) writes:

>This is a problem that I keep running into in one form
>or another:
 

>Let's say we want to define a bonus: One percent of the
>gross is going to go straight into the pocket of whoever
>made the sale.
 

>So we might do something like this to get the bonus for each
>member of the sales staff.
>
> SELECT employee, sum(amount) * 0.01 AS bonus
> FROM sales
> GROUP BY employee
 

>For some reason or other, we want this stored in the 'emp'
>table. How do we do it? Something like this is never going
>to work, in Oracle, is it?
 

> UPDATE emp
> SET
> bonus = ( SELECT employee AS current_employee,
> sum(amount) * 0.01 AS bonus
> FROM sales
> GROUP BY employee
> )
> WHERE emp.employee = current_employee

>The subselect and the WHERE condition don't talk to each
>other, and it's just going to say "Invalid column name"
>on the WHERE.

>My contention is that there is no way to do this in Oracle's
>SQL, and that you need to use a scripting language in the
>solution (probably perl in my case, maybe PL/SQL if I were
>desperate for speed).

Ah, the ace DBA Jay Stanley of the SFPUG group had the answer. I need to look up "correlated subqueries", as found in the Oracle docs (technet registration required):

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/state27a.htm#2067717

So this does what I was looking for:

   UPDATE emp e
   SET bonus =

     (SELECT sum(amount) * 0.01
      from sales s
      where s.employee = e.employee);