Re: Updating a large table (Recursively?)

From: <sjakobson_at_my-dejanews.com>
Date: Thu, 13 Aug 1998 21:12:44 GMT
Message-ID: <6qvksc$ud5$1@nnrp1.dejanews.com>

Actually, all you need to do is

UPDATE your_table
SET ORDINDEX = DECODE(SIGN(ORDINDEX-Deleted_ORDINDEX),

                            0, TOTALORDERS,
                            1, ORDINDEX - 1
                     )

WHERE ORDINDEX >= Deleted_ORDINDEX;

If Deleted_ORDINDEX = 4, NUMORDERS = 7 and TOTALORDERS=15, the above statement will not touch rows with ORDINDEX=1,2 and 3, will set ORDINDEX=15 for the "deleted" ORDINDEX=4 and decrement ORDINDEX for all rows with ORDINDEX between 5 and 15. This way you end up updating just one column.

Solomon Yakobson.
P.S. You still need to decrement NUMORDERS.

In article <6qtag1$3h6$1_at_nnrp1.dejanews.com>,   staylor_at_petrolsoft.com wrote:
> 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
>

-----== 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 - 16:12:44 CDT