title: beginner to sql plus - what am i doing wrong in this query? why am I getting the result as "0 records updated"?
body: <p>I am trying some question in sql plus in oracle but it is not working and not returning the desired result. can someone please tell me what I am doing wrong?</p>
  
  <p>the question is:-</p>
  
  <p>Develop a query that will identify and mark inactive those customers that have bills overdue by more than 30 days (this can usually be done using a sub-query). Remember, you are only marking a customer as inactive, not actually deleting the customer record from the system.</p>
  
  <p>This is the query I have tried:-</p>
  
  <pre><code>SELECT CUSTID, CUSTFIRSTNAME, CUSTSTATUS FROM CUSTOMER;
  
  UPDATE CUSTOMER
  SET CUSTSTATUS='I'
  WHERE CUSTID IN
  (SELECT CUSTID FROM BILLING
   WHERE (SYSDATE - DUEDATE) &gt; 30 AND PAIDDATE IS NULL);
  
  
  SELECT CUSTID, CUSTFIRSTNAME, CUSTSTATUS FROM CUSTOMER;
  </code></pre>
  
  <p>values in billing table are these:-</p>
  
  <pre><code>--INSERT STATEMENTS FOR TABLE BILLING
  
  
  INSERT INTO BILLING(CUSTID,BILLID,BILLAMT,DUEDATE,PAIDAMT,PAIDDATE )
  VALUES(1, 1, 30, DATE '2012-07-01', 30, DATE '2012-07-01' );
  
  INSERT INTO BILLING(CUSTID,BILLID,BILLAMT,DUEDATE,PAIDAMT,PAIDDATE )
  VALUES(2, 2, 80, DATE '2012-06-25', 0,  );
  
  INSERT INTO BILLING(CUSTID,BILLID,BILLAMT,DUEDATE,PAIDAMT,PAIDDATE )
  VALUES(3, 3, 50, DATE '2012-04-01', 0,  );
  
  INSERT INTO BILLING(CUSTID,BILLID,BILLAMT,DUEDATE,PAIDAMT,PAIDDATE )
  VALUES(4, 4, 30, DATE '2012-06-11', 30, DATE '2012-06-11' );
  
  INSERT INTO BILLING(CUSTID,BILLID,BILLAMT,DUEDATE,PAIDAMT,PAIDDATE )
  VALUES(5, 5, 50, DATE '2012-04-30', 0,  );
  
  INSERT INTO BILLING(CUSTID,BILLID,BILLAMT,DUEDATE,PAIDAMT,PAIDDATE )
  VALUES(6, 6, 80, DATE '2012-06-01', 80, DATE '2012-05-30' );
  
  INSERT INTO BILLING(CUSTID,BILLID,BILLAMT,DUEDATE,PAIDAMT,PAIDDATE )
  VALUES(7, 7, 30, DATE '2012-06-15', 0,  );
  
  INSERT INTO BILLING(CUSTID,BILLID,BILLAMT,DUEDATE,PAIDAMT,PAIDDATE )
  VALUES(8, 8, 30, DATE '2012-05-30', 0,  );
  
  INSERT INTO BILLING(CUSTID,BILLID,BILLAMT,DUEDATE,PAIDAMT,PAIDDATE )
  VALUES(9, 9, 80, DATE '2012-05-25', 0,  );
  
  INSERT INTO BILLING(CUSTID,BILLID,BILLAMT,DUEDATE,PAIDAMT,PAIDDATE )
  VALUES(10, 10, 50, DATE '2012-04-01', 0,  );
  </code></pre>
  
  <p>these are the structures of BILLING and Customer table:-</p>
  
  <pre><code>SQL&gt; desc customer;
   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)
  
  SQL&gt; desc billing;
   Name                                                                     Null?    Type
   ------------------------------------------------------------------------ -------- -----------------
   CUSTID                                                                   NOT NULL NUMBER(5)
   BILLID                                                                   NOT NULL NUMBER(5)
   BILLAMT                                                                  NOT NULL NUMBER(5)
   DUEDATE                                                                  NOT NULL DATE
   PAIDAMT                                                                           NUMBER(5)
   PAIDDATE                                                                          DATE
  </code></pre>
  
  <p>I also tried to write <code>" WHERE (SYSDATE - DUEDATE) &gt; 30 AND PAIDDATE ='');"</code></p>
  
  <p>this didn't work either.</p>
  
  <p>EDIT:-</p>
  
  <p>I inserted NULL in my INSERT statements now and I am getting this error now:-</p>
  
  <p>UPDATE CUSTOMER
  *
  ERROR at line 1:
  ORA-02290: check constraint (D03318785.CC_CUSTOMER_F_CUSTSTATUS) violated</p>
  
  <p>can anyone plz help?</p>
  
  <p>Create customer table is as follows:-</p>
  
  <pre><code>CREATE TABLE CUSTOMER(
      custid      NUMBER(5) NOT NULL
      CONSTRAINT pk_custid PRIMARY KEY,
      custstatus      CHAR(1) NOT NULL,
      custdeleterzn   VARCHAR2(100),
      custdeletedate  DATE,
      empid       NUMBER(5) NOT NULL,
      custfirstname   VARCHAR2(30) NOT NULL,
      custlastname    VARCHAR2(20) NOT NULL,
      custstartdate   DATE,
      packid      NUMBER(2) NOT NULL,
      custphone       VARCHAR2(12),
      custstreet      VARCHAR2(30),
      custcity        VARCHAR2(20),
      custstate       CHAR(2),
      custzip     NUMBER(5),
      custemail       VARCHAR2(30));
  </code></pre>