SQL/PL Help!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

SQL/PL Help!!!

Author

Topic

n0r3gr3tz
Starting Member

1 Post

Posted - 2009-04-27 : 10:01:27

CUST- customer_num, first_name, last_name
DELIVERY - customer_num, free_delivery
INVOICE- customer_num, delivery, item_num
INVENTORY - item_num, price

using just these columns from these tables i have to create a detailed report as to which customers have spent over $700 using a procedure or a function...I then will have to go back into the delivery table and change free_delivery to "YES"

I'm new to sql/pl and i've have tried so many different ways if any one could help me even begin with what directon i should go, I would be thankful

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-27 : 10:10:19

We're a SQL Server site, so can't provide much help with PL/SQL, but regular SQL will do the trick:

UPDATE DELIVERY SET free_delivery='YES'
WHERE customer_num IN(
SELECT C.customer_num
FROM CUST C
INNER JOIN INVOICE INV ON C.customer_num=INV.customer_num
INNER JOIN INVENTORY INVT ON INV.item_num=INVT.item_num
GROUP BY C.customer_num
HAVING SUM(INVT.price)>700)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources