Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Updating field in one table based on field in another table

    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!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    consider joining CUSTOMER.CUSTID & BILLING.CUSTID in addition to what you already have
    Last edited by anacedent; 02-10-12 at 20:49.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    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.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    single quote marks must be used in pairs
    There is no need to used double quote marks.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    That was a typo, good catch!! Now running the same command I am getting SQL Command not properly ended/

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2012
    Posts
    188
    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?
    Last edited by jo15765; 02-10-12 at 21:09.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SET (custactivestatus,CustInactiveDate) = ('No' ,SYSDATE)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Feb 2012
    Posts
    188
    Absolutely perfect thank you!

    3 days of my work, solved in 30 minutes!!!!! http://www.dbforums.com/db_images_v3...ilies/beer.gif

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •