Insert problem

Hi all, I have a table which has around 1 lac records. I have to insert datas in the middle. The user sees the content of this table through a data grid in the web application (JSP page). (it is not a connected architecture). The user will add new row in the grid any where . while retrieving i should maintain the newly added position and show the newly added data in exactly the same position as he added. The table structure is like this Recipient_table ( rowid int, values LIST ) right now we are updating the rowid like rowid = rowid +1 where rowid > inserted position and insert the data with the id. Is there any better way of doing it .. like linked list.

I'm not sure which server version you are using. If you are using a server version which supports serial column, that should suffice. Whenever a row inserted into the table with serial column, automatically the serial column is incremented. For more information refer to the documentation. FYI: When you are writing to IIUG, do not use lakh or crore instead use 100,000 or 100 million (i'm not sure if 1 crore is 100 million, check that out your self). "VIJAYAKUMAR GANESH KUMAR" <ganesh17jan@gmail.com> Sent by: ids-bounces@iiug.org 03/07/2007 05:16 AM Please respond to ids@iiug.org To ids@iiug.org cc Subject Insert problem [8575] Hi all, I have a table which has around 1 lac records. I have to insert datas in the middle. The user sees the content of this table through a data grid in the web application (JSP page). (it is not a connected architecture). The user will add new row in the grid any where . while retrieving i should maintain the newly added position and show the newly added data in exactly the same position as he added. The table structure is like this Recipient_table ( rowid int, values LIST ) right now we are updating the rowid like rowid = rowid +1 where rowid > inserted position and insert the data with the id. Is there any better way of doing it .. like linked list. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

i think you have got the question wrong. He is trying to insert a row in between two rows. say we have 1,2,3,4,5 as rowid . while inserting at position 2. he is trying to update the rowid by 1 like 1,3,4,5,6 and then inserts with rowid 2. I am not sure if this is correct approach.. I think you should be concerned about retrieval rather than the insert.

You cannot use serial unless you change the structure to a linked list by serial number (BTW rowid is a poor choice for a column name as IDS has an internal record address which is called rowid and you will be hiding it - OH and values is a keyword also). The other alternative is to renumber every row after the insert position. Either way the procedure is non-trivial. The renumber option is simpler to code, but will use more resources as many rows will need to be updated with each insert/deletion. The linked list option is lower overhead and faster but will be harder to code. For the update case, something like you current structure will work. An insert becomes: update Recipient_table set rowid = rowid + 1 where rowid > <rowid beforeinserted row>; insert into Recipient_table (rowid, values) values ( <rowid before inserted row> + 1, <values list> ); For the linked list you'll want to use a serial number and change the table structure to: create table Recipient_table ( serial sequence; integer previous; integer next; LIST data_values; ); Then an insert becomes: insert into Recipient_table ( sequence, previous, next, data_values ) values ( 0, <before sequence>, <after sequence>, <values list> ); update Recipient_table set next = <new sequence> where sequence = <before sequence>; update Recipient_table set previous = <new sequence> where previous = <before sequence>; Art S. Kagel ----- Original Message ----- From: Vijayakumar Ganesh Kumar <ids@iiug.org> At: 3/07 6:17:38 Hi all, I have a table which has around 1 lac records. I have to insert datas in the middle. The user sees the content of this table through a data grid in the web application (JSP page). (it is not a connected architecture). The user will add new row in the grid any where . while retrieving i should maintain the newly added position and show the newly added data in exactly the same position as he added. The table structure is like this Recipient_table ( rowid int, values LIST ) right now we are updating the rowid like rowid = rowid +1 where rowid > inserted position and insert the data with the id. Is there any better way of doing it .. like linked list. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.