Home » SQL & PL/SQL » SQL & PL/SQL » update two table using for loop
update two table using for loop [message #444216] Fri, 19 February 2010 07:05 Go to next message
dndoon
Messages: 4
Registered: February 2010
Location: USA
Junior Member
dear all,


i need your help with a peice of code. I want to update column in table 1 based on a substraction of two column, one from the same table and the other from different table. Then update the result of substraction in table 1. Number of rows in two tables are different.

--for r in (( select (table2.y - table1.y as x  from table1, table2 where table1.x = c and table2,.x = m))

declare 

  i number := 1;
  c number ;
  m number; 
  
begin 

  select MAX(x) into c from table1; 
  select MAX(x) into table2;

  for r in ((select (table2.y - table1.y)as x
  from table1, table2 where table1.x = c and table2.x = m ) ) LOOP 

    update table1
    set y = (select table1.y - table1.y from table1, table2)
    where x = c ; 
    c := c-1 ;
    m := m-1 ;
    i := i+1 ;

    if mod (i,10000) = 0 THEN 
      COMMIT ;
    end if;

  end loop;
  commit;

end;

please correct my code or let me know if there is an easier way to do the same thing.

Regards.



CM: Added code tags and formatted the code. Please do so yourself next time - see the orafaq forum guide if you're not usre how.

[Updated on: Fri, 19 February 2010 07:19] by Moderator

Report message to a moderator

Re: update two table using for loop [message #444217 is a reply to message #444216] Fri, 19 February 2010 07:18 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I assume this:
select MAX(x) into table2;

should be:
select MAX(x) into m from table2;


Always copy and paste your code, it makes it a lot harder to help if we have to guess what bits are missing.

EDIT: typo

[Updated on: Fri, 19 February 2010 07:23]

Report message to a moderator

Re: update two table using for loop [message #444220 is a reply to message #444216] Fri, 19 February 2010 07:23 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
And on a closer look:
update table1
set y = (select table1.y - table1.y from table1, table2)
where x = c ; 

should be:
update table1
set y = r.x
where x = c ; 


I strongly suggest using longer, more descriptive variable names because this epic short hand is just confusing.

Also there appears to be no direct link between the two tables - you're just starting with the max(x) on each and working your way down. Are you really sure that's right?
Re: update two table using for loop [message #444222 is a reply to message #444216] Fri, 19 February 2010 07:39 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
And I'm afraid that FOR LOOP makes no sense what so ever.

I think at this point you really need to:
1) Restate your problem in more detail.
2) Supply a test case - create table statements and insert statements for data, along with the expected output.

I can't work out what you are really trying to acomplish with this code at all. Give us enough information to work with and we'll sort out your problem.
Re: update two table using for loop [message #444234 is a reply to message #444222] Fri, 19 February 2010 09:16 Go to previous messageGo to next message
dndoon
Messages: 4
Registered: February 2010
Location: USA
Junior Member
hi,

bellow are the two tables descriptions

-- This is my main table

CREATE TABLE MPR_MAS (
MPR_MAS_ID NUMBER(38) NOT NULL,
DEPT_ID NUMBER(38) NOT NULL,
SEG_ID NUMBER(38) NOT NULL,
FIN_DIM_ID NUMBER(38) NOT NULL,
TREE_ID NUMBER(38),
CAT VARCHAR2(150),
ACTUAL_YTD VARCHAR2(15),
ACTUAL_YTD_ADJ VARCHAR2(15),
ACTUAL_ADJ_DESC VARCHAR2(250),
ACTUAL_END_BAL VARCHAR2(15),
BUDGET_YTD VARCHAR2(15),
BUDGET_YTD_ADJ VARCHAR2(15),
BUDGET_END_BAL VARCHAR2(15),
YTD_VAR VARCHAR2(15),
YTD_VAR_PERC VARCHAR2(15),
ACTUAL_MTD VARCHAR2(15),
ACTUAL_MTD_ADJ VARCHAR2(15),
BUDGET_MTD VARCHAR2(15),
MTD_VAR VARCHAR2(15),
MTD_VAR_PERC VARCHAR2(5),
PE_BAL VARCHAR2(15),
AVG_BAL VARCHAR2(15),
INCOME_COST VARCHAR2(15),
YIELD_PERC VARCHAR2(5),
FTP VARCHAR2(15),
FTP_PERC VARCHAR2(5),
COL VARCHAR2(15),
COL_PERC VARCHAR2(5),
NIM VARCHAR2(15),
NIM_PERC VARCHAR2(5),
DATE1 DATE);

ALTER TABLE MPR_MAS ADD (
PRIMARY KEY (MPR_MAS_ID),
CONSTRAINT FK_MPR_DEPT FOREIGN KEY (DEPT_ID) REFERENCES DEPT (DEPT_ID),
CONSTRAINT FK_MPR_FIN FOREIGN KEY (FIN_DIM_ID) REFERENCES FIN_DIM (FIN_DIM_ID),
CONSTRAINT FK_MPR_SEG FOREIGN KEY (SEG_ID) REFERENCES DEPT (DEPT_ID),
CONSTRAINT FK_MPR_TREE FOREIGN KEY (TREE_ID) REFERENCES TREE (TREE_ID));

CREATE OR REPLACE TRIGGER mpr_trig
before insert on mpr_mas
for each row
begin
select mpr_seq.nextval into :new.mpr_mas_id from dual;
end;


-- This is contain the history of my main table.

CREATE TABLE MPR_MAS_HIST (
MPR_MAS_HIST_ID NUMBER(38) NOT NULL,
DEPT_ID NUMBER(38) NOT NULL,
SEG_ID NUMBER(38),
FIN_DIM_ID NUMBER(38) NOT NULL,
TREE_ID NUMBER(38),
CAT VARCHAR2(150),
ACTUAL_YTD_H VARCHAR2(15),
ACTUAL_YTD_ADJ VARCHAR2(15),
ACTUAL_ADJ_DESC VARCHAR2(250),
ACTUAL_END_BAL_H VARCHAR2(15),
BUDGET_YTD VARCHAR2(15),
BUDGET_YTD_ADJ VARCHAR2(15),
BUDGET_END_BAL VARCHAR2(15),
YTD_VAR VARCHAR2(15),
YTD_VAR_PERC VARCHAR2(15),
ACTUAL_MTD VARCHAR2(15),
ACTUAL_MTD_ADJ VARCHAR2(15),
BUDGET_MTD VARCHAR2(15),
MTD_VAR VARCHAR2(15),
MTD_VAR_PERC VARCHAR2(5),
PE_BAL VARCHAR2(15),
AVG_BAL VARCHAR2(15),
INCOME_COST VARCHAR2(15),
YIELD_PERC VARCHAR2(5),
FTP VARCHAR2(15),
FTP_PERC VARCHAR2(5),
COL VARCHAR2(15),
COL_PERC VARCHAR2(5),
NIM VARCHAR2(15),
NIM_PERC VARCHAR2(5),
DATE1 DATE);

ALTER TABLE MPR_MAS_HIST ADD (
PRIMARY KEY (MPR_MAS_HIST_ID));
-----------------------------------------



MPR_MAS_HIST_ID starts from 1 , 2 ,3 , 4 ........ to 126 for example. MPR_MAS table is starting the MPR_MAS_ID from 127 , 128,.....



all i need is to substract the value of ACTUAL_END_BAL in MPR_MAS from ACTUAL_END_BAL_H in MPR_MAS_HIST then store it in
actual_mtd in MPR_MAS


i hope you can help me with this query

Regards,
Re: update two table using for loop [message #444237 is a reply to message #444216] Fri, 19 February 2010 09:29 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) please read the orafaq forum guide, particularly the section about formatting your post, then repost your test case using code tags, it makes it a lot easier to read. I did it for you last time but I don't want to have to keep doing it.

2) What is the relationship between the two tables? How do you know which MPR_MAS_HIST records correspond to which MPR_MAS records?
Re: update two table using for loop [message #444244 is a reply to message #444216] Fri, 19 February 2010 09:54 Go to previous messageGo to next message
dndoon
Messages: 4
Registered: February 2010
Location: USA
Junior Member
There is no relationship between the two tables. I moved data to the table MPR_MAS_HIS to keep prevoius months deatails. In MPR_MAS table i keep only this month data. I did that to facilitate the subsraction for myself. Do you think there is no need to do this.


I am sorry about tha last post.


Regards,
Re: update two table using for loop [message #444247 is a reply to message #444234] Fri, 19 February 2010 10:07 Go to previous messageGo to next message
dndoon
Messages: 4
Registered: February 2010
Location: USA
Junior Member
hi,

bellow are the two tables descriptions

-- This is my main table
CREATE TABLE MPR_MAS (
MPR_MAS_ID NUMBER(38) NOT NULL,
DEPT_ID NUMBER(38) NOT NULL,
SEG_ID NUMBER(38) NOT NULL,
FIN_DIM_ID NUMBER(38) NOT NULL,
TREE_ID NUMBER(38),
CAT VARCHAR2(150),
ACTUAL_YTD VARCHAR2(15),
ACTUAL_YTD_ADJ VARCHAR2(15),
ACTUAL_ADJ_DESC VARCHAR2(250),
ACTUAL_END_BAL VARCHAR2(15),
BUDGET_YTD VARCHAR2(15),
BUDGET_YTD_ADJ VARCHAR2(15),
BUDGET_END_BAL VARCHAR2(15),
YTD_VAR VARCHAR2(15),
YTD_VAR_PERC VARCHAR2(15),
ACTUAL_MTD VARCHAR2(15),
ACTUAL_MTD_ADJ VARCHAR2(15),
BUDGET_MTD VARCHAR2(15),
MTD_VAR VARCHAR2(15),
MTD_VAR_PERC VARCHAR2(5),
PE_BAL VARCHAR2(15),
AVG_BAL VARCHAR2(15),
INCOME_COST VARCHAR2(15),
YIELD_PERC VARCHAR2(5),
FTP VARCHAR2(15),
FTP_PERC VARCHAR2(5),
COL VARCHAR2(15),
COL_PERC VARCHAR2(5),
NIM VARCHAR2(15),
NIM_PERC VARCHAR2(5),
DATE1 DATE);

ALTER TABLE MPR_MAS ADD (
PRIMARY KEY (MPR_MAS_ID),
CONSTRAINT FK_MPR_DEPT FOREIGN KEY (DEPT_ID) REFERENCES DEPT (DEPT_ID),
CONSTRAINT FK_MPR_FIN FOREIGN KEY (FIN_DIM_ID) REFERENCES FIN_DIM (FIN_DIM_ID),
CONSTRAINT FK_MPR_SEG FOREIGN KEY (SEG_ID) REFERENCES DEPT (DEPT_ID),
CONSTRAINT FK_MPR_TREE FOREIGN KEY (TREE_ID) REFERENCES TREE (TREE_ID));

CREATE OR REPLACE TRIGGER mpr_trig
before insert on mpr_mas
for each row
begin
select mpr_seq.nextval into :new.mpr_mas_id from dual;
end;


-- This is contain the history of my main table.

CREATE TABLE MPR_MAS_HIST (
MPR_MAS_HIST_ID NUMBER(38) NOT NULL,
DEPT_ID NUMBER(38) NOT NULL,
SEG_ID NUMBER(38),
FIN_DIM_ID NUMBER(38) NOT NULL,
TREE_ID NUMBER(38),
CAT VARCHAR2(150),
ACTUAL_YTD_H VARCHAR2(15),
ACTUAL_YTD_ADJ VARCHAR2(15),
ACTUAL_ADJ_DESC VARCHAR2(250),
ACTUAL_END_BAL_H VARCHAR2(15),
BUDGET_YTD VARCHAR2(15),
BUDGET_YTD_ADJ VARCHAR2(15),
BUDGET_END_BAL VARCHAR2(15),
YTD_VAR VARCHAR2(15),
YTD_VAR_PERC VARCHAR2(15),
ACTUAL_MTD VARCHAR2(15),
ACTUAL_MTD_ADJ VARCHAR2(15),
BUDGET_MTD VARCHAR2(15),
MTD_VAR VARCHAR2(15),
MTD_VAR_PERC VARCHAR2(5),
PE_BAL VARCHAR2(15),
AVG_BAL VARCHAR2(15),
INCOME_COST VARCHAR2(15),
YIELD_PERC VARCHAR2(5),
FTP VARCHAR2(15),
FTP_PERC VARCHAR2(5),
COL VARCHAR2(15),
COL_PERC VARCHAR2(5),
NIM VARCHAR2(15),
NIM_PERC VARCHAR2(5),
DATE1 DATE);

ALTER TABLE MPR_MAS_HIST ADD (
PRIMARY KEY (MPR_MAS_HIST_ID));



MPR_MAS_HIST_ID starts from 1 , 2 ,3 , 4 ........ to 126 for example. MPR_MAS table is starting the MPR_MAS_ID from 127 , 128,.....



all i need is to substract the value of ACTUAL_END_BAL in MPR_MAS from ACTUAL_END_BAL_H in MPR_MAS_HIST then store it in
actual_mtd in MPR_MAS


i hope you can help me with this query

Regards,
Re: update two table using for loop [message #444249 is a reply to message #444247] Fri, 19 February 2010 10:54 Go to previous message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>all i need is to substract the value of ACTUAL_END_BAL in MPR_MAS from ACTUAL_END_BAL_H in MPR_MAS_HIST then store it in actual_mtd in MPR_MAS

How do you know/determine which row from MPR_MAS gets subtracted from which row of MPR_MAS_HIST?
Previous Topic: Integer as Decimal
Next Topic: Sum Daily Total to Weekly in Oracle
Goto Forum:
  


Current Time: Thu Sep 29 09:02:05 CDT 2016

Total time taken to generate the page: 0.13712 seconds