CODEWITHSUNDEEP
sqloracle-database
Gina Buffkin
Gina Buffkin

Reputation: 1

Trying to figure out how to use UPDATE in SQL

I'm just learning SQL so I still have a long way to go to figure everything out. I'm trying to write an UPDATE in a customer table that shows any customer 30 days delinquent on their account and will make them inactive.

My table consists of: Customerid, Firstname, Lastname, Startdate, Billingcycle, Phone, Streetaddress, City, State, Zip, Employeeid, Status, Reason, Statusdate, and Email.

I tried the following, but no luck;

UPDATE customer
SET status = (
SELECT status
FROM customer
WHERE status = billingcycle + 30)
WHERE status = 'INACTIVE';

Any help here?

Upvotes: 0

Views: 110

Answers (3)

Meredith Poor
Meredith Poor

Reputation: 351

update customer 
set status = 'INACTIVE' 
 where getdate() > dateadd(dd, 30, billingcycle);`

Unfortunately, I'm not sure how this is expressed in Oracle because my background is in SQL Server. The getdate() could be replaced with a cutoff date parameter. Billing cycle will need to be a date column, and you will need to add 30 days to that date using something like a dateadd function. At any rate, if the left hand side is less than the last billing cycle + 30 days, then they're overdue.

Upvotes: 0

Jasti
Jasti

Reputation: 947

UPDATE customer
SET status = 'INACTIVE'
WHERE billingcycle < TRUNC(SYSDATE) - 30

Upvotes: 1

Edper
Edper

Reputation: 9322

Let's assume you have the BillingDate as the basis for checking deliquency:

UPDATE customer
SET status = 'INACTIVE'
WHERE (trunc(sysdate)-BilingDate) = 30

And if it is 30 days an above you just change = to >= like:

UPDATE customer
SET status = 'INACTIVE'
WHERE (trunc(sysdate)-BilingDate) >=30

Or inferring from your post the column is BillingCycle then you can change BillingDate to BillingCycle like:

UPDATE customer
SET status = 'INACTIVE'
WHERE (trunc(sysdate)-Bilingcycle) >=30

Upvotes: 0

Related Questions