Solved

Oracle Update column based on an expression

Posted on 2014-10-04
6
278 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

786 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