Updating field in one table based on field in another table

jo15765 · #1 (**permalink**) 02-10-12, 18:41

I have been trying to figure out this statement for 3 days now and am no further along than I was Wed!

What I am trying to do is update the value of ActiveStatus in my table Customer based on the value of BillDueDate in my Billing Table. The problem I am having is there is no ForiegnKey to link the two tables together!

here are my tables:
SQL> CREATE TABLE BILLING (
2 BillID NUMBER(5) NOT NULL,
3 CustID NUMBER(5) NOT NULL,
4 BillDate DATE,
5 BillDueDate DATE,
6 BillPaidDate DATE,
7 BillDueAmt NUMBER(5,2),
8 BillPaidAmt NUMBER(5,2),
9 BillStatus VARCHAR2(8),
10 CONSTRAINT BILLING_BILLID_PK PRIMARY KEY (BillID));

SQL> CREATE TABLE CUSTOMER (
2 CustID NUMBER(5) NOT NULL,
3 CustLname VARCHAR2(25),
4 CustFname VARCHAR2(20),
5 CustAddr VARCHAR2(25),
6 CustCity VARCHAR2(25),
7 CustState CHAR(2),
8 CustZip CHAR(5),
9 CustPhone NUMBER(10),
10 CustEmail VARCHAR2(35),
11 CustSDate DATE,
12 CustActiveStatus VARCHAR2(5),
13 EmpID NUMBER(5) NOT NULL,
14 CustInactDate DATE,
15 CustInactReason VARCHAR2(250),
16 CONSTRAINT CUSTOMER_CUSTID_PK PRIMARY KEY (CustID));

I have tried this code, plus numerous variations and I can't seem to get it!

Code:

UPDATE Customer SET CustActiveStatus = 'NO' WHERE BillDueDate IN (Select BillDueDate FROM Billing) WHERE BillDueDate IN ((sysdate-30) > BillDueDate);

I feel like I am almost there, but by George am I not hitting it!

anacedent · #2 (**permalink**) 02-10-12, 19:42

consider joining CUSTOMER.CUSTID & BILLING.CUSTID in addition to what you already have

jo15765 · #3 (**permalink**) 02-10-12, 19:47

I tried to execute this:

Code:

UPDATE Customer Set CustActiveStatus = 'No" WHERE CustID IN (Select CustID from Billing WHere sysdate-30 > BillDueDate) INNER JOIN Customer.Custid = Billing.Custid;

And I am getting an ORACLE error of quoted string not properly terminated.

anacedent · #4 (**permalink**) 02-10-12, 19:50

single quote marks must be used in pairs
There is no need to used double quote marks.

jo15765 · #5 (**permalink**) 02-10-12, 19:53

That was a typo, good catch!! Now running the same command I am getting SQL Command not properly ended/

anacedent · #6 (**permalink**) 02-10-12, 19:57

how does SQL below work?

Code:

UPDATE customer 
SET    custactivestatus = 'No' 
WHERE  custid IN (SELECT custid 
                  FROM   billing 
                  WHERE  SYSDATE - 30 > billduedate 
                         AND customer.custid = billing.custid)

jo15765 · #7 (**permalink**) 02-10-12, 20:05

To Perfection! Thank you!

Actually one more change --- I also need to updae my CustInactiveDate to be Date()...how would that be added to the above SQL?