Home » SQL & PL/SQL » SQL & PL/SQL » updating multiple rows in loop (10.2.0.3.0)
updating multiple rows in loop [message #391363] Wed, 11 March 2009 23:30 Go to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
SQL> create table test(start_time date,grace_time number);

Table created.

SQL> Insert into TEST values(TO_DATE('2009-03-11 11:30:00','yyyy-mm-dd hh24:mi:ss'), 16);

1 row created.

SQL> Insert into TEST values(TO_DATE('2009-03-11 11:30:00','yyyy-mm-dd hh24:mi:ss'), 15);

1 row created.


SQL> create table test1(start_time date,grace_time number);

Table created.

SQL> Insert into TEST1 values(TO_DATE('2009-03-09 11:30:00','yyyy-mm-dd hh24:mi:ss'), 16);

1 row created.

SQL> Insert into TEST1 values(TO_DATE('2009-03-09 11:30:00','yyyy-mm-dd hh24:mi:ss'), 15);

1 row created.

create or replace procedure testproc as

Cursor abc is SELECT * from test; 

begin

for hop in abc loop

update test1 set start_time=hop.START_TIME Where GRACE_TIME=hop.GRACE_TIME;

dbms_output.put_line('Grace Time is ' || hop.GRACE_TIME);

end loop;

commit;

end;


i have to update both the columns in test1 based on grace_time.
using the above procedure.only one row is getting updated.
please suggest me how to update both the columns.
Re: updating multiple rows in loop [message #391365 is a reply to message #391363] Wed, 11 March 2009 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>please suggest me how to update both the columns.
Color me dense.
I do not understand the "rules" of how/why each column is updated.
Please explain/describe each column separately.
Re: updating multiple rows in loop [message #391372 is a reply to message #391363] Thu, 12 March 2009 00:10 Go to previous messageGo to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
grace_time field type is number.

there are different grace_times
we have to update the start_time for corresponding grace time
Re: updating multiple rows in loop [message #391375 is a reply to message #391363] Thu, 12 March 2009 00:20 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>grace_time field type is number.
OK This I understand.


>there are different grace_times
OK I understand this.


>we have to update the start_time for corresponding grace time
Please explain/elaborate.

Re: updating multiple rows in loop [message #391377 is a reply to message #391372] Thu, 12 March 2009 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
we have to update the start_time for corresponding grace time

Is this not what your procedure does?
In addition, you can do it in a single update.

By the way, your update statement is meaningless from a functional point of view. Is this an exercise?

Regards
Michel

Re: updating multiple rows in loop [message #391378 is a reply to message #391372] Thu, 12 March 2009 00:28 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@laknar,

You claimed,
Quote:

using the above procedure.only one row is getting updated.



But I see the start_time column of both the records getting updated.
SQL> SELECT * FROM test_tab;

START_TIME           GRACE_TIME
-------------------- ----------
11-MAR-2009 11:30:00         16
11-MAR-2009 11:30:00         15

2 rows selected.

SQL> SELECT * FROM test_tab_new;

START_TIME           GRACE_TIME
-------------------- ----------
09-MAR-2009 11:30:00         16
09-MAR-2009 11:30:00         15

2 rows selected.

SQL> EXEC testproc;
Grace Time is 16
Grace Time is 15

PL/SQL procedure successfully completed.

SQL> SELECT * FROM test_tab_new;

START_TIME           GRACE_TIME
-------------------- ----------
11-MAR-2009 11:30:00         16
11-MAR-2009 11:30:00         15

2 rows selected.


Please post your complete SQL Plus session running the procedure and showing the results of the target table (the table which is updated by the procedure) before and after the procedure run.

Regards,
Jo
Re: updating multiple rows in loop [message #391379 is a reply to message #391363] Thu, 12 March 2009 00:33 Go to previous messageGo to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
can we update in a statement using multiple values?
problem is i have two update statements within the loop(Consecutive)

first update statement is executing successfully.
where as second is not getting updated.

Re: updating multiple rows in loop [message #391381 is a reply to message #391379] Thu, 12 March 2009 00:43 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@laknar,

laknar wrote on Thu, 12 March 2009 11:03
can we update in a statement using multiple values?
problem is i have two update statements within the loop(Consecutive)

first update statement is executing successfully.
where as second is not getting updated.



Unless you post the exact statements with the actual and desired outputs you can't expect anyone to give a proper solution. People in this forum don't like to play guessing game.

How can we know what the second update does without seeing it?

Now, please post the complete procedure along with the actual and desired outputs.

Regards,
Jo

[Updated on: Thu, 12 March 2009 00:44]

Report message to a moderator

Re: updating multiple rows in loop [message #391387 is a reply to message #391363] Thu, 12 March 2009 01:05 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
i have to update both the columns in test1 based on grace_time.
using the above procedure.only one row is getting updated.
please suggest me how to update both the columns.


Seems like OP is confused between Row and Column

Normally It is Not Possible to update both the columns (ALL the columns) unless there is some common values between those tables.

Current script updates both the rows (ALL the rows) . It can also be done by using single SQL.

Re: updating multiple rows in loop [message #391390 is a reply to message #391363] Thu, 12 March 2009 01:28 Go to previous messageGo to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
SQL> create table test(start_time date,grace_time number,ind varchar2(1));

Table created.

SQL> Insert into TEST values(TO_DATE('2009-03-12 11:30:00','yyyy-mm-dd hh24:mi:ss'), 16,'Y');

1 row created.

SQL> Insert into TEST values(TO_DATE('2009-03-12 11:30:00','yyyy-mm-dd hh24:mi:ss'), 15,'Y');

1 row created.


SQL> create table test1(start_time date,grace_time number);

Table created.

SQL> Insert into TEST1 values(TO_DATE('2009-03-09 11:30:00','yyyy-mm-dd hh24:mi:ss'), 16);

1 row created.

SQL> Insert into TEST1 values(TO_DATE('2009-03-09 11:30:00','yyyy-mm-dd hh24:mi:ss'), 15);

1 row created.

SQL> create or replace procedure testproc as
  2  
  3  Cursor abc is SELECT * from test where ind='Y'; 
  4  
  5  begin
  6  
  7  for hop in abc loop
  8  
  9  update test set start_time=(hop.START_TIME+1),ind='N' Where GRACE_TIME=hop.GRACE_TIME;
 10  
 11  update test1 set start_time=hop.START_TIME Where GRACE_TIME=hop.GRACE_TIME;
 12  
 13  dbms_output.put_line('Grace Time is ' || hop.GRACE_TIME);
 14  
 15  end loop;
 16  
 17  commit;
 18  
 19  end;
 20  /

Procedure created.

SQL> execute testproc;
Grace Time is 15
Grace Time is 16


PL/SQL procedure successfully completed.

Re: updating multiple rows in loop [message #391402 is a reply to message #391390] Thu, 12 March 2009 02:26 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@laknar,

Both updates are working fine.
SQL> SELECT * FROM test_tab;

START_TIME           GRACE_TIME I
-------------------- ---------- -
12-MAR-2009 11:30:00         16 Y
12-MAR-2009 11:30:00         15 Y

2 rows selected.

SQL>  SELECT * FROM test_tab_new;

START_TIME           GRACE_TIME
-------------------- ----------
11-MAR-2009 11:30:00         16
11-MAR-2009 11:30:00         15

2 rows selected.

SQL> EXEC testproc;
Grace Time is 16
Grace Time is 15

PL/SQL procedure successfully completed.

SQL> SELECT * FROM test_tab; -- First Update updates test_tab

START_TIME           GRACE_TIME I
-------------------- ---------- -
13-MAR-2009 11:30:00         16 N
13-MAR-2009 11:30:00         15 N

2 rows selected.

SQL> SELECT * FROM test_tab_new; -- 2nd update updated test_tab_new

START_TIME           GRACE_TIME
-------------------- ----------
12-MAR-2009 11:30:00         16
12-MAR-2009 11:30:00         15

2 rows selected.



As you can see from the difference in output both updates worked fine. The cursor was initialized holding the START_TIME column value of '12-MAR-2009'. So that data was used by both your updates.

Regards,
Jo
Re: updating multiple rows in loop [message #391409 is a reply to message #391363] Thu, 12 March 2009 03:23 Go to previous messageGo to next message
microsoft_fly
Messages: 5
Registered: March 2009
Location: hz.zj.china
Junior Member

just like:
update a
set (f1,f2)=(select ff1,ff2 from b where id=a.id)
where exists(select 1 from b where id=a.id)
Re: updating multiple rows in loop [message #391418 is a reply to message #391363] Thu, 12 March 2009 04:02 Go to previous messageGo to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
initially no rows found to update.
now i used trim functions on where columns and its working.
thanks.
Re: updating multiple rows in loop [message #391422 is a reply to message #391418] Thu, 12 March 2009 04:11 Go to previous messageGo to next message
microsoft_fly
Messages: 5
Registered: March 2009
Location: hz.zj.china
Junior Member

Please print your sql
Re: updating multiple rows in loop [message #391434 is a reply to message #391418] Thu, 12 March 2009 04:40 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@laknar,

laknar wrote on Thu, 12 March 2009 14:32
initially no rows found to update.
now i used trim functions on where columns and its working.
thanks.


I am pretty much confused. Why use TRIM Function on Date or Number datatype columns? What good it will do? Shocked

Regards,
Jo
Previous Topic: SQL - spaces in COLs
Next Topic: declare and define cursor in different procedures (merged)
Goto Forum:
  


Current Time: Mon Dec 05 12:52:27 CST 2016

Total time taken to generate the page: 0.13388 seconds