Home » SQL & PL/SQL » SQL & PL/SQL » updating Nth row and adding a column (merged 2) (Oracle 10g, Win7)
updating Nth row and adding a column (merged 2) [message #562958] Tue, 07 August 2012 05:55 Go to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
I had created a table which have 100s' of entries in it.
create table reg_user
(
USERNAME VARCHAR2(50),
PASSWORD VARCHAR2(20)
)
***
insert all
into reg_user (USERNAME,PASSWORD) values ('A','aa')
into reg_user (USERNAME,PASSWORD) values ('B','vv')
into reg_user (USERNAME,PASSWORD) values ('C','ff')
into reg_user (USERNAME,PASSWORD) values ('D','ee') 
--hundreds of ROWS inserted
select * from dual;


1. In this table, how to update the Nth row, how can I do it

2. Now I need to add USERID in this table,which will get value from 1 to max no. of rows. I do not want to drop the table
and again re create it adding USERID or update each row manually.
Is there any other way to add USERID and have IDs from 1 to max IDs.
Re: updating Nth row and adding a column [message #562959 is a reply to message #562958] Tue, 07 August 2012 05:59 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
1. You'd use UPDATE statement with the WHERE clause which would identify Nth row (if you know the rule to identify it).

2. Alter a table in order to add a column. Then update it with a value (for example, ROWNUM might do the job here).
alter table reg_user add userid number;

update reg_user set 
  userid = rownum;
Re: updating Nth row and adding a column [message #562964 is a reply to message #562959] Tue, 07 August 2012 06:08 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
1. I've to update the table when there is no USERID but not after adding USERID to the table.

2. And also
Quote:
alter table reg_user add userid number;

I've to give USERID manually.How to add USERID column so that values from 1 to max no. IDs are added directly to the table.
Re: updating Nth row and adding a column [message #562966 is a reply to message #562964] Tue, 07 August 2012 06:11 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I believe that your only concern is #2: ALTER table + UPDATE its records (which I showed you in my previous message). Is that not what you are looking for?
Re: updating Nth row and adding a column [message #562967 is a reply to message #562964] Tue, 07 August 2012 06:11 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) And why is that a problem?
2) What do you mean by "I've to give USERID manually"? What's wrong with Littlefoot's solution?
Re: updating Nth row and adding a column [message #562968 is a reply to message #562964] Tue, 07 August 2012 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1.
What you say is meaningless.
How can you update a table when there is nothing to update?

2.
Once again this is meaningless and inconsistent.

Regards
Michel
Re: updating Nth row and adding a column [message #562971 is a reply to message #562968] Tue, 07 August 2012 06:23 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
Guys either you did not understand my question or i can not explain my problem to you.
Anyway for my first question, I've found the solution.
1.suppose I need to update the third row.
 UPDATE reg_user SET PASSWORD='zzz' WHERE rowid=(
SELECT rowid FROM reg_user WHERE rownum <=3 
MINUS
SELECT rowid FROM reg_user
 WHERE rownum <3
 );

Is there any better way to do this.

2. but I can not solve my second question.
@littlefoot
Quote:
alter table reg_user add userid number;

gives USERID value null and I've to insert IDs in the table. I want to add USERID column and get the IDs in it automatically.
Re: updating Nth row and adding a column [message #562973 is a reply to message #562971] Tue, 07 August 2012 06:30 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) That does not update the 3rd row. That updates a random row. Rows in a table have no inherent order. You need a column to order by. You don't appear to have one.
2) You get the userid values by running the update littlefoot suggested. There is no single command that will add the column and populate it.
Re: updating Nth row and adding a column [message #562974 is a reply to message #562973] Tue, 07 August 2012 06:32 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As of "automatic" USERID values: are you talking about future (automatic) updates? If so, that could be done with a database trigger.
incrementally increasing userid value via looping [message #563232 is a reply to message #562958] Fri, 10 August 2012 05:15 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
I've a table
create table t1
(
USERNAME VARCHAR2(50),
PASSWORD VARCHAR2(20)
)
***
insert all
into t1(USERNAME,PASSWORD) values ('A','aa')
into t1(USERNAME,PASSWORD) values ('B1','vv')
into t1(USERNAME,PASSWORD) values ('C1','iiii')
into t1(USERNAME,PASSWORD) values ('D1','ee') 
--other rows
select * from dual;

after creating the row I have to add userid column to the table which will be unique for all the rows.
I've inserted the first entry for the USERID column i.e 1.

Is there any way in oralce such that I select the max userid and then loops until the last row,
incrementally adding userid values to each consecutive rows.
  • Attachment: tabl.PNG
    (Size: 4.52KB, Downloaded 34 times)
Re: updating Nth row and adding a column [message #563239 is a reply to message #562974] Fri, 10 August 2012 06:16 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
Thank you guys for your suggestions.
Re: incrementally increasing userid value via looping [message #563240 is a reply to message #563232] Fri, 10 August 2012 06:18 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
This is not a new question but a continuation of your previous one so I've merged the topics.
Littefoot has shown you the simplest way to do this.
Alternatively you can use a for loop and update each individually.
Re: incrementally increasing userid value via looping [message #563241 is a reply to message #563240] Fri, 10 August 2012 06:25 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
@cookiemonster: my question is what you said as the solution. How can I use a for loop and update each individually for doing this??
Re: incrementally increasing userid value via looping [message #563243 is a reply to message #563241] Fri, 10 August 2012 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to use a loop when you can do it in a simple and single UPDATE?

Regards
Michel
Re: incrementally increasing userid value via looping [message #563245 is a reply to message #563241] Fri, 10 August 2012 06:33 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
nischalinn wrote on Fri, 10 August 2012 12:25
How can I use a for loop and update each individually for doing this??

That's about the most basic PL/SQL you can write. If you don't know how to code a for loop I suggest you spend some time reading the documentation. It has examples.
Try it. If you get stuck post what you tried here and we'll show you where you've gone wrong.


Re: incrementally increasing userid value via looping [message #563246 is a reply to message #563245] Fri, 10 August 2012 06:34 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Hint - FOR UPDATE and WHERE CURRENT OF clauses will help
Re: incrementally increasing userid value via looping [message #563353 is a reply to message #563246] Mon, 13 August 2012 03:02 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
guys I tried this, but got the error:
I do not know whether my approach is right or wrong. Please suggest me the right way to solve my problem.

DECLARE   
  CURSOR c1 IS
   SELECT username FROM reg_user
    FOR update;
  init NUMBER(10);
  lRow NUMBER(10);

begin 
   SELECT Max(userid) into init FROM reg_user;
   SELECT Count(*) INTO lRow FROM reg_user; 
   init:= init+1;
 -- Dbms_Output.put_line(init);
 -- Dbms_Output.put_line(lRow);  

 FOR i IN init..lRow LOOP
 UPDATE reg_user SET id = i WHERE CURRENT OF c1;
 END LOOP;     
END;
*******************
ORA-01001: invalid cursor
ORA-06512:
Re: incrementally increasing userid value via looping [message #563354 is a reply to message #563353] Mon, 13 August 2012 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your approch is completly wrong.

Regards
Michel
Re: incrementally increasing userid value via looping [message #563358 is a reply to message #563354] Mon, 13 August 2012 03:21 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
@Michel, thank you for the reply.
Please suggest me the right way.

Thank You.
Re: incrementally increasing userid value via looping [message #563359 is a reply to message #563358] Mon, 13 August 2012 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 10 August 2012 13:27
Why do you want to use a loop when you can do it in a simple and single UPDATE?

Regards
Michel

Re: incrementally increasing userid value via looping [message #563365 is a reply to message #563359] Mon, 13 August 2012 04:26 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
@Michel, I did not understand your question. how can I use SINGLE UPDATE for all the rows?? i think I've to write update statement for each rows.
I tried the following, but it updates all the rows with max id.

DECLARE 
uname varchar2(15);  
  CURSOR c1 IS
   SELECT username FROM reg_user
    FOR update;
  init NUMBER(10);
  lRow NUMBER(10);   
BEGIN 
   SELECT Max(id) into init FROM reg_user;
   SELECT Count(*) INTO lRow FROM reg_user; 
   init:= init+1;
 -- Dbms_Output.put_line(init);
 -- Dbms_Output.put_line(lRow); 
 OPEN c1;
 LOOP
 FETCH c1 INTO uname;
 EXIT WHEN (c1%notfound); 
 FOR i IN init..lRow LOOP  
 UPDATE reg_user SET id = i WHERE CURRENT OF c1;
  END LOOP;
 END loop;    
 CLOSE c1;   
END;

Will any one help me with this code??
Thank You.
Re: incrementally increasing userid value via looping [message #563367 is a reply to message #563365] Mon, 13 August 2012 04:33 Go to previous messageGo to next message
John Watson
Messages: 4569
Registered: January 2010
Location: Global Village
Senior Member
I can;t stand this any longer.
orcl> update scott.emp set sal=rownum;

14 rows updated.

orcl> select ename,sal from emp;

ENAME             SAL
---------- ----------
SMITH               1
ALLEN               2
WARD                3
JONES               4
MARTIN              5
BLAKE               6
CLARK               7
SCOTT               8
KING                9
TURNER             10
ADAMS              11
JAMES              12
FORD               13
MILLER             14

14 rows selected.

orcl>

Re: incrementally increasing userid value via looping [message #563368 is a reply to message #563367] Mon, 13 August 2012 04:37 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It's being said a week ago (but he just won't listen).
Re: incrementally increasing userid value via looping [message #563370 is a reply to message #563368] Mon, 13 August 2012 04:58 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
Guys thank all of you for the reply. Don't go so rude...
I can do it with the solution you provided but I just want to learn to do the same thing using loop. that's all.

cookiemonster gave me the hint to do the same, and I tried but did not succeed. Is it a sin to try another approach for a problem???
Quote:
Hint - FOR UPDATE and WHERE CURRENT OF clauses will help

@John Watson, you are a senior member and you might have many rights as well sound knowledge of SQL but never forget everything is complex before it becomes easy...

Thank You.

[Updated on: Mon, 13 August 2012 04:59]

Report message to a moderator

Re: incrementally increasing userid value via looping [message #563371 is a reply to message #563370] Mon, 13 August 2012 05:10 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK then, here you are.

SQL> create table reg_user as
  2    select ename from emp;

Table created.

SQL> alter table reg_user add userid number;

Table altered.

SQL> declare
  2    cursor cur_e is
  3      select ename from reg_user
  4      for update of userid;
  5
  6    i number := 1;
  7
  8  begin
  9    for cur_r in cur_e loop
 10      update reg_user set
 11        userid = i
 12        where current of cur_e;
 13
 14      i := i + 1;
 15    end loop;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL> select * from reg_user
  2  order by userid;

ENAME          USERID
---------- ----------
SMITH               1
ALLEN               2
WARD                3
JONES               4
MARTIN              5
BLAKE               6
CLARK               7
SCOTT               8
KING                9
TURNER             10
ADAMS              11
JAMES              12
FORD               13
MILLER             14

14 rows selected.

SQL>
Re: incrementally increasing userid value via looping [message #563372 is a reply to message #563371] Mon, 13 August 2012 05:20 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
@Littlefoot thank you very much.
just a little query,
what is the role of cur_r, while it is not used anywhere after declaration??

[Updated on: Mon, 13 August 2012 05:43]

Report message to a moderator

Re: incrementally increasing userid value via looping [message #563373 is a reply to message #563372] Mon, 13 August 2012 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some good readings for you:

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: incrementally increasing userid value via looping [message #563374 is a reply to message #563373] Mon, 13 August 2012 05:44 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
Thank You Michel...
Re: incrementally increasing userid value via looping [message #563490 is a reply to message #563374] Tue, 14 August 2012 07:04 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
@Littlefoot:
Will you please explain me the use of cur_r.

Quote:
for cur_r in cur_e loop
Re: incrementally increasing userid value via looping [message #563491 is a reply to message #563490] Tue, 14 August 2012 07:11 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Sorry, didn't see your question.

It is implicitly declared record (you don't declare it explicitly in DECLARE section) with a %ROWTYPE attribute for a cursor CUR_E. In this example, it is only CUR_R.ENAME that you can reference as CUR_E is declared as "select EMPNO from ..."
Re: incrementally increasing userid value via looping [message #563559 is a reply to message #563491] Tue, 14 August 2012 22:27 Go to previous message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
@Littlefoot, thank you.
Previous Topic: CTE
Next Topic: data types to store large integer values
Goto Forum:
  


Current Time: Sun Sep 21 09:27:30 CDT 2014

Total time taken to generate the page: 0.07591 seconds