Updating a table with an array returned from a select

From: Joe Brenner <doom_at_kzsu.stanford.edu>
Date: 26 Jul 2001 23:25:12 GMT
Message-ID: <9jq8so$6b6$1@usenet.Stanford.EDU>

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). Received on Thu Jul 26 2001 - 18:25:12 CDT