1. Home
  2. Oracle
  3. Oracle Sql Insert Statement?

Oracle Sql Insert Statement?

By admin Posted in: Oracle

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));

«
»
  1. Anonymous Says

    UPDATE Customer Set CustActiveStatus = ‘No’ WHERE CustID IN (Select CustID from Billing WHERE BillDueDate < sysdate-30);
    In other words: if a customer has at least one bill unpaid for 30 days, that customer is marked as “not acvtive”.

  2. Offline Says

    Well, seeing as you have CustID fields in both tables, why don’t you make that the foreign key in the Billing table. Then, you could simply check the BillDueDate field of the Billing table where the CustIDs are equal and then change the CustActiveStatus field appropriately.
    EDIT:
    UPDATE Customer Set CustActiveStatus = ‘No” WHERE CustID IN (Select CustID from Billing WHere sysdate-30 BillDueDate);
    You have ” instead of ‘ after No in your UPDATE statement. Replace it with an ‘ and try it again.

  3. Ratchetr Says

    Doesn’t CustID link the 2 tables together? The fact that it wasn’t defined as a foreign key constraint doesn’t change the fact that they are related. Constraints just enforce database integrity, they don’t define what you can and can’t do in an SQL statement.
    I think you want something along these lines:
    UPDATE CUSTOMER
    SET CustActiveStatus = ’somevalue’
    WHERE CustID IN (
      select CustID
      from BILLING
      where
    );
    ETA:
    Yes, replace the ” with a ‘.
    But this is also wrong:
    WHere sysdate-30 BillDueDate
    You need something between sysdate-30 and BillDueDate. > is probably what you want.

Leave a Reply

You must be logged in to post a comment.

More Interesting Things

Oracle

©2011 Fave Soft, All rights reserved.