Re: Updating a large table (Recursively?)

Hi,
perhaps there's more to it than I could graps at first reading, but I think that what
you're trying to do is basicallly wrong, because you should never move large data
in such a way. Instead, I suggest that you set up a small two- or three-column table that
could work like a kind of index into your large table, that is, it could contain the PK of the
large table, the ORDINDEX and perhaps a validity flag. Then the administrative work
with the validity could be done on the small table. You could also set up a view over
the two tables to see, at any given moment, only the valid rows from the large table
in the right order.

In such a way, the large table would be left intact all the time.

I know this is only an idea, but I hope it will help you,

--
Pavel Polcar

staylor_at_petrolsoft.com wrote in article <6qtag1$3h6$1_at_nnrp1.dejanews.com>...
> 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 Thu Aug 13 1998 - 03:34:16 CDT