TABLE UPDATE

[bubu2006]

I HAVE ONE TABLE CALLED A

ACCT# NAME ACCT_STATUS

10 ROY A

20 SEAN A

28 BILL DA

26 SAM A

40 LINDA A

12 MIKE A

98 BOB DA

I WANT TO UPDATE THIS TABLE. SO THAT TABLE WILL UPDATE ACCT# AND NAME COLUMN. THE ACCT NUMBER BEWTWEEN 10 TO 90 NEED TO UPDATE LIKE 1,2,3... AND NAME LIKE NAME1,NAME2,NAME3 WHERE ACCT_STATUS IS EQUAL TO A.


SO THE NEW TABLE WILL LOOK LIKE

ACCT# NAME ACCT_STATUS

1 NAME1 A

2 NAME2 A

3 NAME3 A

4 NAME4 A

5 NAME5 A

COULD ANYONE HELP ME TO WRITE THE SCRIPT?

I GUESS I NEED ONE VARIABLE LIKE TO UPDATE ACCOUNT NUMBER

VAR1:=0
ACCT#:=VAR1+1

CAN I WRITE SINGLE UPDATE STATEMENT OR I NEED CURSOR OR ANY OTHER PL/SQL?

 

[SantaMufasa]

Bubu,

If I understand correctly what you want, then you can do it in a single SQL UPDATE statement:

SQL> select * from a;

     ACCT# NAME       STATU
---------- ---------- -----
        10 ROY        A
        20 SEAN       A
        28 BILL       DA
        26 SAM        A
        40 LINDA      A
        12 MIKE       A
        98 BOB        DA

7 rows selected.

SQL> update a set acct# = rownum, name = 'NAME'||rownum
  2   where acct# between 10 and 90 and status = 'A';

5 rows updated.

SQL> select * from a;

     ACCT# NAME       STATU
---------- ---------- -----
         1 NAME1      A
         2 NAME2      A
        28 BILL       DA
         3 NAME3      A
         4 NAME4      A
         5 NAME5      A
        98 BOB        DA

7 rows selected.

Let us know if this is what you wanted.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[bubu2006]

this is fine. but i am suspecting tow num will not work for me.

 

[SantaMufasa]

What leads you to that presumption? (I did not pay extra to Oracle to get ROWNUM to work for me where it doesn't work for others. <grin>)

I there something peculiar about your need that you did not mention in your posting?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[bubu2006]

. In reality the record may enter into the table in any order. But still I need this order when I will update the table.

If you run

Select * from A

You could see different order. I have just post an example.

I know u did not pay extra money to Larry. But my row 1 can store any acc#.

I did not explain clearly at fisrt time. Sorry for that. Thanks a lot for your help.

 

[SantaMufasa]

Bubu,

You certainly can dictate a consistent order via use of a consistent "ORDER BY" clause. ROWNUM is simply a numeric value that corresponds to the ordinal position of a row returning from a query..as you know, ROWNUM does not represent a specific, static row identifier for a given row in the table.

If you don't want to use ROWNUM, then what algorithm do you wish to use in its place?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]