Insertion and Deletion of rows

the_gautam · Posted: Wed Jan 23, 2008 4:22 pm

hi,
can anyone help me in my requirement?

i have a table with a column in which integers are stored in ascending order. whenever a new row is inserted into the table, it reads the maximum value available in the table and adds 1 to it to insert the new row. i need to delete the row with the smallest value in that column with every insertion. its like the GDG versions, when the new one is created the oldest one gets deleted.

William Thompson · Posted: Wed Jan 23, 2008 4:27 pm

Just like you select the max value, you can select the min value. Then just delete it....

the_gautam · Posted: Wed Jan 23, 2008 11:07 pm

i ve tried it. i can do it in cobol-db2 batch program. however, not getting it done in spufi.

mkk157 · Active User Joined: 17 May 2006 Posts: 310

Gautam,

U can achieve this through:

1) SPUFI:

U can run the 2 queries one time.
Before inserting a new row into the table u can run another query to delete the row with the least integer value as you said.

2) TRIGGER:

U can write a trigger to delete the row with the least integer value, when ever user tries to enter a new row into the table.

Correct me If I am wrong...

the_gautam · Posted: Thu Jan 24, 2008 3:09 pm

thanks for the suggestion. can someone please give me the query?

stodolas · Posted: Thu Jan 24, 2008 6:42 pm

DELETE FROM table WHERE intcolumn = min(intcolumn);
INSERT ....

Phrzby Phil · Posted: Thu Jan 24, 2008 6:50 pm

Since you can do it in COBOL, you must know how to write the SQL.

Since you are still asking, however, perhaps we can send someone over to type it in for you?