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): ===