This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: sfpug@sf.pm.org
From: Joe Brenner <doom@kzsu.stanford.edu>
Subject: [sf-perl] [OT] SQL question
Date: Thu, 26 Jul 2001 15:45:56 -0700
Sorry about the not-very-perl topic here, but we were
talking about this a bit at the sfpug pizza meeting, so I
thought I would lay my SQL question on the list.
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 (preferably perl, maybe PL/SQL if one were
desperate for speed).
===
To: sfpug@sf.pm.org, Joe Brenner <doom@kzsu.stanford.edu>
From: Jay Stanley <beansboy@yahoo.com>
Subject: Re: [sf-perl] [OT] SQL question
Date: Thu, 26 Jul 2001 17:08:39 -0700 (PDT)
Joe - I'm not sure if this'll make it to sfpug as I'm
posting from another place, so I'm sending this to you
directly as well.
What you need is a correlated subquery. Try this:
UPDATE emp e
SET bonus =
(SELECT sum(amount) * 0.01
from sales s
where s.employee = e.employee);
Does that give you what you're expecting?
-jay stanley
-Oracle DBA
-http://www.dbspecialists.com/
===
To: "'sfpug@sf.pm.org'" <sfpug@sf.pm.org>
From: Erin Conel <Erin@planetU.com>
Subject: RE: [sf-perl] [OT] SQL question
Date: Thu, 26 Jul 2001 17:07:03 -0700
This *is* possible in Oracle. It's called a correlated subquery. If you have
access to Oracle Documentation it's covered in the SQL Reference stuff under
the UPDATE statement. If I understand your question correctly, the following
is the update statement you'll want to use. You get the 'Invalid Column
Name' error because their should be a join in the subquery to tie the sales
and emp tables together. Also, you're columns in the update statement don't
match the subquery, so that would have given you an error as well. (There's
no 'employee' column referenced in your set clause, but it's selected in
your subquery.) As always, test it first.
UPDATE emp
SET bonus = (SELECT sum(amount)*0.01 AS bonus
FROM sales
WHERE sales.employee = emp.employee
GROUP BY sales.employee)
WHERE emp.employee = current_employee
;
===
To: sfpug@sf.pm.org
From: Kirit Sarvaiya <kirits@yahoo.com>
Subject: Re: [sf-perl] [OT] SQL question
Date: Thu, 26 Jul 2001 17:34:54 -0700 (PDT)
You're right. PL/SQL (faster) or Perl would have to
be employed.
Which is why....
SQL = Structured QUERY Language
PL/SQL = Programmable Language/Structured Query
Language
perl = Practical Extraction and Reporting Language
(amongst others)
===
To: sfpug@sf.pm.org
From: Kirit Sarvaiya <kirits@yahoo.com>
Subject: RE: [sf-perl] [OT] SQL question
Date: Thu, 26 Jul 2001 17:38:45 -0700 (PDT)
Erin,
In your query, what's current_employee?
Seems to me that your current_employee is defined
elsewhere
like: var current_employee = 1;
I believe Joe is asking about updating ALL employees
in the emp table.
Kirit.
===
To: sfpug@sf.pm.org
From: Joe Brenner <doom@kzsu.stanford.edu>
Subject: Jay Stanley: Re: [sf-perl] [OT] SQL question
Date: Thu, 26 Jul 2001 18:20:35 -0700
Yes, Erin is right, "correlated subqueries" are indeed what
I need to look up. There's an example of their use here
(you need to register to get an oracle "technet" account to
see this):
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/state27a.htm#2067717
This is what I get for trying to rely on the O'Reilly book,
"Oracle SQL The Essential Reference". This is definitely
not one of the good O'Reilly's...
Jay Stanley also pointed me in that direction, but his
message didn't make it to the list (and yes Kirit, I am
trying to update all the employee records in one shot,
that's exactly what Jay's code does):
===