Updating a large table (Recursively?)

Hi there,

  I was hoping I could get some feedback and ideas on how I should do an update in my database. I am creating a web application that accesses my Oracle database. I am using the PL/SQL cartridge in Orace Web Application Server 3.0.

  Here's the deal: I have a large table with about 80 columns. One of the columns is called ORDINDEX. This is used to order the rows. My procedure I am writing is REMOVE_ORDER, which will be "removing" one of the rows, and then moving all subsequent rows up. However, the way we work it is that we have 15 rows already in the database, and we have a column called NUMORDERS which let's us know how many of the 15 rows are valid. So, given NUMORDERS=7, only rows with ORDINDEX=1-7 would be valid. However, rows with ORDINDEX=8-15 would still exist, but the data in them would be junk to us. Therefore, I never actually do INSERT or DELETE, but rather I always do an update.

     So, in the procedure, I will be "removing" a given row by copying all the data from subsequent rows to the previous row. So, if I'm "removing" ORDINDEX=4, I copy all the columns from ORDINDEX=5 into ORDINDEX=4, all the columns from 6 to 5...and so on through ORDINDEX=15. I then decrement NUMORDERS.   So, my questions is this: What would be the simplest way to do this? We decided that inserting and deleting rows is not an option. It has to be done with UPDATE. The basic way is to assign every single column one by one. But this is really long. We also thought of doing a SELECT * INTO temptable FROM table. Then, I would do an update like this: UPDATE table set column1=temptable.column1, column2=temptable.column2, etc. We also thought of doing cursors, but I'm not really sure how that would work. We were particularly wondering if there's a way that I can do a SELECT statement and be able to access the results immediately after in an UPDATE statement.

Thanks,

Scott Taylor
Petrolsoft Corporation

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Aug 12 1998 - 19:03:13 CDT