Re: Update question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 08 Jun 2001 11:13:32 -0400
Message-ID: <9pq1its33pkk9m23463p05jd9qaabqmmne@4ax.com>

A copy of this was sent to benetnasch_at_blimey.co.uk (Benetnasch) (if that email address didn't require changing) On 8 Jun 2001 02:09:08 -0700, you wrote:

>I posted a message a while back about updating duplicate rows
>in a table, but I don't think I explained the scenario very
>well, so here's version 2.0:
>
>I have a table which can have duplicate rows in it. I need
>to update these rows with an identifier.
>
>Here's an example of a duplicate row (table_a):
>
>Card_no Amount
>12345 20.00
>12345 20.00
>
>(2 transactions of 20.00 on card no 12345; total 40.00)
>
>I then need to update a batch and item number in this
>table with batch and term numbers held in another
>table (table_b). Eg:
>
>Card_no Batch_no Item_no
>12345 100 1
>12345 100 2
>
>Therefore, I can't write something like this:
>
>UPDATE table_a
>SET (table_a.Batch_no, table_a.Item_no) =
>(SELECT table_b.Batch_no
>, table_b.Item_no
>FROM table_b
>WHERE table_a.Card_no = table_b.Card_no)
>
>... because it'll update both rows in table_a with the same
>data:
>
>Card_no Amount Batch_no Item_no
>12345 20.00 100 2
>12345 20.00 100 2
>
>...rather than assigning different values to each of the
>2 rows in table_a:
>
>Card_no Amount Batch_no Item_no
>12345 20.00 100 1
>12345 20.00 100 2
>
>... which is what I'm trying to do.
>
>I need to do this update without changing the existing table
>structure, as the system is a "legacy system" and is owned
>by another company.
>
>Thanks for any assistance,
>
>Benetnasch

If every row just needs some number, it could be as easy as this:

ops$tkyte_at_ORA8I.WORLD> select * from t;

         X Y
---------- ----------

         1
         1
         1
         2
         2

ops$tkyte_at_ORA8I.WORLD> update t set y = rownum;

5 rows updated.

ops$tkyte_at_ORA8I.WORLD> select * from t;

         X Y
---------- ----------

         1          1
         1          2
         1          3
         2          4
         2          5

If the rows need to be numbered WITHIN the duplicate values (and you have Oracle8i release 8.1.6 and up -- why oh why is the version never included???) you can do this

ops$tkyte_at_ORA8I.WORLD> declare
  2 type rc is ref cursor;
  3

  4      l_cursor rc;
  5      l_rid    rowid;
  6      l_rn     number;
  7  begin
  8      open l_cursor for
  9      'select rowid rid, row_number() over ( partition by x
 10                                             order by x ) rn
 11             from t';
 12  
 13      loop
 14          fetch l_cursor into l_rid, l_rn;
 15          exit when l_cursor%notfound;
 16  
 17          update t set y = l_rn where rowid = l_rid;
 18      end loop;
 19      close l_cursor;

 20 end;
 21 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> select * from t;

         X Y
---------- ----------

         1          1
         1          2
         1          3
         2          1
         2          2

ops$tkyte_at_ORA8I.WORLD>

Pre-8.1.6, you would have to maintain your own counter and reset it to one when X changes in the loop

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Fri Jun 08 2001 - 10:13:32 CDT