Oracle

I have a problem. I have a 'Meeting' table whereby people attend all types of diferent 'Meetings'. Several 'Meetings' run a week but a particular 'Meeting' will not run twice on the same day. I have given the 'Meetings' unique idenfiers by using a composite primary key consisting of the 'MEETING_NAME' and the 'MEETING_DATE' it is to be held e.g. Gamblers Anonymous, 26/APR/2004. The problem is i wish to have 4 weeks worth of 'Meetings' in the database at one time, so basically i want the database to delete the contents of the 'Meeting' table once the 'MEETING_DATE' is four weeks old. Also when it deletes the old dates i would like it to create new dates. I will provide some sample data below so you have a better understanding. In the sample data below i have only showed one week which starts on the Monday the 26th of April and finishes on Sunday the 2nd of May. In reality i will require there to be four weeks worth of classes. As you can see the only data that will change below is the date as the meetings week by week have the same name, day it is held on, time and duration.

MEETING TABLE

MEETING_NAME MEETING_DATE DAY TIME DURATION

Gamblers Anonymous 26/APR/2004 MONDAY 6pm 2 HOURS
Alcoholic Anonymous 26/APR/2004 MONDAY 8pm 2 HOURS
Smokers Anonymous 27/APR/2004 TUESDAY 6pm 2 HOURS
Overeaters Anonymous 27/APR/2004 TUESDAY 8pm 2 HOURS
Cocaine Anonymous 28/APR/2004 WEDNESDAY 6pm 2 HOURS
Marijuana Anonymous 29/APR/2004 THURSDAY 4pm 2 HOURS
Marijuana Anonymous 30/APR/2004 FRIDAY 5pm 2 HOURS
Sexaholics Anonymous 30/APR/2004 FRIDAY 8pm 2 HOURS
Parents Anonymous 01/MAY/2004 SATURDAY 5pm 2 HOURS
Emotions Anonymous 02/MAY/2004 SUNDAY 4pm 2 HOURS

Please people help me out :-)

If I understand you correctly, you want to "shift" the data four weeks into the future.

Try to add 4 weeks to your date (4 Weeks = 28 days):

UPDATE meeting_table SET meeting_date=meeting_date + 28

Im not too sure if thats what im after. I think that my be part of the solution to my problem, i will just provide some more information about the scenerio just in case. I basically have four weeks worth of Meetings in my database. At the end of each month i would like the system to automatically generate new dates for the next four weeks worth of Meetings using the same data as before except with the new dates. So i believe the system date will need to be involved in the query, is that correct?

Let me see if I understand:

Let's assume the table "starts" with 01/APR/2004. Then your table holds meeting data for 28 days, that is up to 27/APR/2004.

At the end of april (e.g. 28/APR/2004) you want that your dates "moves" so that they begin with 28/APR/2004.

If you want your data to begin with "today", it's a little bit more complex:

UPDATE meeting_table SET meeting_date=sysdate + (meeting_date-(SELECT min(meeting_date) FROM meeting_table))

should work

WoW, that looks complex. Im going to type that command in and see if it does the job. But cheers anyway.