I need help with an Oracle subquery?

Question

Develop a query that will identify and mark for deletion those customers that have bills overdue by more than 14 days (you may need a series of SQL statements to accomplish this, but it usually can be done using a sub-query). Remember, you are only marking a customer as inactive, not actually deleting the customer record from the system.

Here are the tables in question:

Name Null? Type
-------------------------------------- -------- ------
CUSTID NOT NULL NUMBER(5)
CUSTSTATUS NOT NULL CHAR(1)
CUSTDELETERZN VARCHAR2(100)
CUSTDELETEDATE DATE
EMPID NOT NULL NUMBER(5)
CUSTFIRSTNAME NOT NULL VARCHAR2(30)
CUSTLASTNAME NOT NULL VARCHAR2(20)
CUSTSTARTDATE DATE
PACKID NOT NULL NUMBER(2)
CUSTPHONE VARCHAR2(12)
CUSTSTREET VARCHAR2(30)
CUSTCITY VARCHAR2(20)
CUSTSTATE CHAR(2)
CUSTZIP NUMBER(5)
CUSTEMAIL VARCHAR2(30)

Name Null? Type
----------------------------------------- -------- ------
CUSTID NOT NULL NUMBER(5)
BILLID NOT NULL NUMBER(5)
BILLAMOUNT NOT NULL NUMBER(5)
DUEDATE NOT NULL DATE
PAIDAMOUNT NUMBER(5)
PAIDDATE DATE

Thanks in advance for any and all help with this.

Answer 1

You're trying to set customer.custstatus, a single field, to an entire subquery, containing 5 columns? Why did you think this would work?



Try setting custstatus to whatever it's supposed to be WHERE CUSTID IN (SELECT CUSTID FROM BILLING WHERE BILLING.DUEDATE....)

Answer 2

UPDATE customer SET

WHERE custid IN

(SELECT custid FROM billing WHERE duedate < sysdate - 14)

Answer 3

You could also use a subquery with the EXISTS clause, like so:-



UPDATE customer c

SET custstatus = deleted

WHERE EXISTS (

SELECT bi.custid

FROM billing bi

WHERE bi.custid = c.custid

AND duedate < sysdate - 14

)

Answer 4

This smells like another homework assignment.



Why don't you give it a try on your own, and if you have difficulties, come back with specific questions?



Remember, this is "Yahoo! Answers" not "Yahoo! Do My Homework For Me"