Home » SQL & PL/SQL » SQL & PL/SQL » Best logic to iterate (Oracle 9i database)
Best logic to iterate [message #568530] Sat, 13 October 2012 03:38 Go to next message
anoopmanagoli
Messages: 20
Registered: August 2012
Location: india
Junior Member
Hi,

I have a scenario, where i need to update the date for 300 records.

The records must be updated in such a way that the month is september and after every 5 records the date must be incremented by one.

Example :
for reocrds 1-5 the date will be 01SEp12
for records 6-10 the date will be 02SEP12
.
.
.

So on so forth until the all 300 records are updated.

Please let me know the possible the best logic to do this.
Re: Best logic to iterate [message #568531 is a reply to message #568530] Sat, 13 October 2012 04:31 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
anoopmanagoli wrote on Sat, 13 October 2012 10:38
The records must be updated in such a way that the month is september and after every 5 records the date must be incremented by one ...



Hi there,


You have to specify what do you mean exacly by "After every five records". Rows in your oracle table are not ordered. So you need to specify a criteria (for example based on the values of another column in your table) in order to be able to group your table rows before being able to update them based on those ordered groups.



Here is an example:

In the following table I can group rows based on the values of a column named groupid:


DROP TABLE testupdate;
CREATE TABLE testupdate
(
  colkey    VARCHAR2(30)    NOT NULL,
  groupid   VARCHAR2(30)    NOT NULL,
  coldate   DATE            NOT NULL   -- So this is the date column that has to be incremented
);
ALTER TABLE testupdate ADD CONSTRAINT PK_TESTUPDATE PRIMARY KEY(colkey);


-- Rows having the same groupid (2nd column of the testupdate table) will be in the same group
-- I chosed SYSDATE just as an example for populating the date column

INSERT ALL
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0001', 'g-0001', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0002', 'g-0001', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0003', 'g-0001', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0004', 'g-0001', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0005', 'g-0001', SYSDATE)
  
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0006', 'g-0002', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0007', 'g-0002', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0008', 'g-0002', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0009', 'g-0002', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0010', 'g-0002', SYSDATE)
  
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0011', 'g-0003', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0012', 'g-0003', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0013', 'g-0003', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0014', 'g-0003', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0015', 'g-0003', SYSDATE)
  
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0016', 'g-0004', SYSDATE)
  INTO testupdate(colkey, groupid, coldate) VALUES ('k-0017', 'g-0004', SYSDATE)
SELECT * FROM DUAL;



SQL> SELECT * FROM testupdate;

COLKEY                 GROUPID                COLDATE
------------------------------ ------------------------------ ---------
k-0001                 g-0001                 13-OCT-12
k-0002                 g-0001                 13-OCT-12
k-0003                 g-0001                 13-OCT-12
k-0004                 g-0001                 13-OCT-12
k-0005                 g-0001                 13-OCT-12
k-0006                 g-0002                 13-OCT-12
k-0007                 g-0002                 13-OCT-12
k-0008                 g-0002                 13-OCT-12
k-0009                 g-0002                 13-OCT-12
k-0010                 g-0002                 13-OCT-12
k-0011                 g-0003                 13-OCT-12
k-0012                 g-0003                 13-OCT-12
k-0013                 g-0003                 13-OCT-12
k-0014                 g-0003                 13-OCT-12
k-0015                 g-0003                 13-OCT-12
k-0016                 g-0004                 13-OCT-12
k-0017                 g-0004                 13-OCT-12

17 rows selected.

SQL>



Now based on the values of groupdid (which allows to group several rows for each value) I
can update the date column as indicated in your post, starting by 01-SEP-2012
UPDATE testupdate t1
SET coldate = 
(
  SELECT    TO_DATE('31-AUG-2012', 'DD-MON-YYYY') 
            + 
            (
              SELECT group_level
              FROM
              (
                SELECT groupid, ROWNUM AS group_level
                FROM
                (
                  SELECT groupid
                  FROM testupdate t4
                  GROUP BY groupid
                ) t3
                ORDER BY groupid ASC
              ) t2
              WHERE t1.groupid = t2.groupid
            )
  FROM DUAL
);




SQL> SELECT * FROM testupdate;

COLKEY                 GROUPID                COLDATE
------------------------------ ------------------------------ ---------
k-0001                 g-0001                 01-SEP-12
k-0002                 g-0001                 01-SEP-12
k-0003                 g-0001                 01-SEP-12
k-0004                 g-0001                 01-SEP-12
k-0005                 g-0001                 01-SEP-12
k-0006                 g-0002                 02-SEP-12
k-0007                 g-0002                 02-SEP-12
k-0008                 g-0002                 02-SEP-12
k-0009                 g-0002                 02-SEP-12
k-0010                 g-0002                 02-SEP-12
k-0011                 g-0003                 03-SEP-12
k-0012                 g-0003                 03-SEP-12
k-0013                 g-0003                 03-SEP-12
k-0014                 g-0003                 03-SEP-12
k-0015                 g-0003                 03-SEP-12
k-0016                 g-0004                 04-SEP-12
k-0017                 g-0004                 04-SEP-12

17 rows selected.

SQL>




The update statement in the example, updates and increments the date column for each group of rows having the same groupid (not necessarily 5 rows in each group). So if you have a distinct groupid for each five rows (while inserting rows in your table) you can then increment the sequence by 5 as asked in the OP.



Regards,
Dariyoosh

[Updated on: Sat, 13 October 2012 06:37]

Report message to a moderator

Re: Best logic to iterate [message #568534 is a reply to message #568531] Sat, 13 October 2012 07:06 Go to previous messageGo to next message
Littlefoot
Messages: 19537
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Maybe I'm not reading it correctly, but ... if there are 300 records and all of them have to belong to September, while day has to be changed for every 5 records, then you are in trouble because 300 / 5 = 60, and there's no September that has 60 days.
Re: Best logic to iterate [message #568535 is a reply to message #568534] Sat, 13 October 2012 07:14 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Based on the two lines he provided

Example :
for reocrds 1-5 the date will be 01SEp12
for records 6-10 the date will be 02SEP12
.
.
.


What I understood (maybe I didn't understand correctly) was that the start point would be 01-SEP-2012 and then it could be incremented as long as needed (towards further date values) based on group of rows. But if he really wants only September for all rows, then yes, you're right there is a problem with OP's concept.

[Updated on: Sat, 13 October 2012 07:20]

Report message to a moderator

Re: Best logic to iterate [message #568539 is a reply to message #568535] Sat, 13 October 2012 08:48 Go to previous messageGo to next message
anoopmanagoli
Messages: 20
Registered: August 2012
Location: india
Junior Member
Hi Dariyoosh and little foot,

Thank you for the advice and help, and sorry for creating a confusion to both...

Now i will give you a real time example:

I have table called MEMBERS.

The columns in tables are Member_id, Code, created_date,description.
The member_id and code are primary keys

Now as i said earlier there are already 300 records in this table.
The date right now for 300 records are random starting from 01JAN12 to current date.


MY objective is that:
1) I have to update the date for all these records in such a way that they fall between 01SEP12 to 30SEP12.
2) So i need to perform two things
a) i am updating the month to that of SEPT
b) and their dates are from 01SEP to 30 SEP12.
c) for any random number of records

We can ignore that condition of date being incremented after every 5 records as rightfully pointed out it will fail.

I am trying out suggestion given by dari.. but but i need to see how will the grouping logic work.... since i have 8 distinct values in the primary key column CODE.

Regards,
Anoop
Re: Best logic to iterate [message #568541 is a reply to message #568539] Sat, 13 October 2012 09:00 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
anoopmanagoli wrote on Sat, 13 October 2012 15:48

MY objective is that:
1) I have to update the date for all these records in such a way that they fall between 01SEP12 to 30SEP12.
2) So i need to perform two things
a) i am updating the month to that of SEPT
b) and their dates are from 01SEP to 30 SEP12.
c) for any random number of records


And how possibly this can be done if there is no criterion for grouping rows? More importantly now, that we know by your own admission that there will be duplicates for date value updates.



Regards,
Dariyoosh

[Updated on: Sat, 13 October 2012 09:02]

Report message to a moderator

Re: Best logic to iterate [message #568542 is a reply to message #568541] Sat, 13 October 2012 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
And how possibly this can be done if there is no criterion for grouping rows?

There is a criterion: "c) for any random number of records".


@anoopmanagoli

Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Best logic to iterate [message #568545 is a reply to message #568530] Sat, 13 October 2012 09:17 Go to previous messageGo to next message
vijay.indap
Messages: 1
Registered: October 2012
Location: chennai
Junior Member
    
update <yourtablename>  set <yourdatecolumn>=(select aaa from (

select rownum a ,aaa from (
select to_datE('01-SEp-2012')+ b.aa aaa from 
 
(select rownum aa from all_objects  where rownum <61) b,(select 1 a from dual union all
select 1 from dual union all
select 1 from dual union all
select 1 from dual union all
select 1 from dual  
) a order by aaa) ) c  where <yourtable_join_column>= c.a )




put some logic c.a column like your id generation is like 'a21' put 'a'||(rownum+20)
Re: Best logic to iterate [message #568548 is a reply to message #568545] Sat, 13 October 2012 10:08 Go to previous message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Problem:
SQL> select to_datE('01-SEp-2012') from dual;
select to_datE('01-SEp-2012') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel
Previous Topic: Updating a duplicate Primarykey
Next Topic: how to write the Query to convert the XML to table
Goto Forum:
  


Current Time: Tue Sep 02 03:30:12 CDT 2014

Total time taken to generate the page: 0.06927 seconds