Solved

Oracle Update column based on an expression

Posted on 2014-10-04
6
271 Views
Last Modified: 2014-10-04
I have a table which has a calculated VARCHAR2 column based on an event date. Yes, I know that this is not advisable.
age_group is a VARCHAR2 column.
start_date is a DATE column.

I need to set the value of the age_group  column.

Psuedocode: Set the value of Table1.age_group  equal to 'LT8'
where the number of days since the start date is between 0 and 7.

UPDATE Table1
SET age_group = ‘LT8’  
WHERE  Table1 .(SysDate-start_date)  BETWEEN 0 AND 7 ;

Is this the correct syntax?  Does (SysDate - start-date) return a number of days, or do I need to apply a conversion function?

Thanks,
0
Comment
Question by:Dovberman
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40361363
UPDATE Table1
SET age_group = ‘LT8’  
WHERE SysDate- to_Date(start_date,'yyyy-mm-dd')  BETWEEN 0 AND 7 ;

before you update first test the records affected by running :

select start_date, SysDate- to_Date(start_date,'yyyy-mm-dd')   as diff
from Table1

WHERE SysDate- to_Date(start_date,'yyyy-mm-dd')  BETWEEN 0 AND 7 ;
0
 

Author Comment

by:Dovberman
ID: 40361382
Thanks,

This is part of a procedure.
Will a count work? I need a count for each of 9 date different groups.

SELECT Count(start_date) as Group1 WHERE SysDate- to_Date(start_date,'yyyy-mm-dd')  BETWEEN 0 AND 7,
 SELECT Count(start_date) as Group2 WHERE SysDate- to_Date(start_date,'yyyy-mm-dd')  BETWEEN 8 AND 30 ;

Returns:
Group1       Group2    ...

Thanks,
27                56
0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40361399
try this, you just need to add additional case statements and sums

select suM(group1) as group1, sum(group2) as group2
from(
SELECT 
case when SysDate- to_Date(start_date,'yyyy-mm-dd')  BETWEEN 0 AND 7 then 1 else 0 end as group1,
case when SysDate- to_Date(start_date,'yyyy-mm-dd')   BETWEEN 8 AND 30 then 1 else 0 end as group2
from table1
)a

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Dovberman
ID: 40361406
Just what I was looking for.
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40361407
Glad that I helped ;)
0
 

Author Closing Comment

by:Dovberman
ID: 40361409
Just what I was looking for.

Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now