Databases
StackOverflow2013
Postsdbo
POChoosing specific values to update by comparing a date with the current date
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POChoosing specific values to update by comparing a date with the current date
    text
    copied!<p>I'm trying to compare a specific date (in this case a billing date) and compare it with the current date (SYSDATE). Any entity whose billing date exceeds the current date by 14 days gets their account status marked as inactive. Here's the code:</p> <pre><code>UPDATE CUSTOMERS; SET ACCOUNT_STATUS = 'Inactive' WHERE CUST_ID IN ( SELECT CUST_ID FROM BILLING WHERE BILLING_DATE IN (SELECT TRUNC(SYSDATE) - TRUNC(BILLING_DATE) DAYS FROM BILLING) &gt; 14) COMMIT; </code></pre> <p>It won't run and I'm not quite sure where the error is. I'm using Oracle 11g Express, and working through the browser, not the command line, so I can't figure out which exact line is wrong. Any advice at all would be appreciated, thanks!</p> <p>EDIT: More Info. Customer Table</p> <pre><code>CREATE TABLE CUSTOMERS ( CUST_ID NUMBER(5), FIRST_NAME CHAR(30), LAST_NAME CHAR(35), ADDRESS CHAR(100), POSTAL_CODE CHAR(8), EMAIL_ADDRESS VARCHAR2(100), DATE_REGISTERED DATE, DATE_DISCONNECTED DATE, REASON_DISC VARCHAR2(250), EMPLOYEE_ID NUMBER(5), SURVEY_ID NUMBER(5), ACCOUNT_STATUS CHAR(20), PRIMARY KEY (CUST_ID), FOREIGN KEY (SURVEY_ID) REFERENCES SURVEY, FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES </code></pre> <p>);</p> <p>sample entry</p> <pre><code>INSERT INTO CUSTOMERS VALUES (1,'Alan','Abdelkarim','4712 Victorian ',' H3W 2N1','Alan.Abdelkarim@hotmail.com',(to_date('02/01/2012','mm/dd/yyyy')),NULL,NULL,NULL,NULL,'Active'); </code></pre> <p>billing table</p> <pre><code>CREATE TABLE BILLING ( TRANSACTION_ID NUMBER(5), PACKAGE_ID NUMBER(5), CUST_ID NUMBER(5), BILLING_DUE DATE, TRANS_TIME CHAR(5), TRANS_DATE Date, PRIMARY KEY (TRANSACTION_ID), FOREIGN KEY (PACKAGE_ID) REFERENCES PACKAGES, FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS </code></pre> <p>);</p> <p>A sample entry</p> <pre><code>INSERT INTO BILLING VALUES (1,4,1,(to_date('11/15/2013','mm/dd/yyyy')),'8:00',(to_date('11/28/2013','mm/dd/yyyy'))); </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload