Home » SQL & PL/SQL » SQL & PL/SQL » update else insert
update else insert [message #254988] Mon, 30 July 2007 06:18 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Hi All
I am using two cursors for updating and inserting respectively in the following procedure.

Could I merge the two loops into one using IF? Please guide me..

Thank you,
  (mp IN char, yp IN number) as
  balance_month varchar2(3);
  balance_year number(4);
  begin
  ------- GET LAST MONTH AND YEAR (IF LAST MONTH IS JAN change year also
  if mp='DEC' then
     balance_month:='NOV';
     balance_year:=yp;
  elsif mp='NOV' then
     balance_month:='OCT';
     balance_year:=yp;
  elsif mp='OCT' then
     balance_month:='SEP';
     balance_year:=yp;
  elsif mp='SEP' then
     balance_month:='AUG';
     balance_year:=yp;
  elsif mp='AUG' then
     balance_month:='JUL';
     balance_year:=yp;
  elsif mp='JUL' then
     balance_month:='JUN';
     balance_year:=yp;
  elsif mp='JUN' then
     balance_month:='MAY';
     balance_year:=yp;
  elsif mp='MAY' then
     balance_month:='APR';
     balance_year:=yp;
  elsif mp='APR' then
     balance_month:='MAR';
     balance_year:=yp;
  elsif mp='MAR' then
     balance_month:='FEB';
     balance_year:=yp;
  elsif mp='FEB' then
     balance_month:='JAN';
     balance_year:=yp;
  elsif mp='JAN' then
     balance_month:='DEC';
     balance_year:=yp-1;
  else
  null;
  end if;
dbms_output.put_line('Balance Month/Year'||balance_month||' '||balance_year);
----------------fetch balance for the last month
   declare
   cursor c1 is select member_id,balance,m,y from net
   where m=balance_month and y=balance_year
   and member_id IN(select member_id from net where m=mp and y=yp);
   
   cursor c2 is select member_id,balance,m,y from net
   where m=balance_month and y=balance_year
   and member_id NOT IN(select member_id from net where m=mp and y=yp);
   begin
  ----------update net if member_id exists
  for x in c1 loop
  update net set arrears=x.balance where member_id=x.member_id and m=mp and y=yp;
  dbms_output.put_line(x.member_id||' '||x.balance||''||mp||' '||yp);
  end loop;
  ----------insert into  net if member_id does not exist
  for x2 in c2 loop
  insert into net(member_id,arrears,m,y) values(x2.member_id,x2.balance,mp,yp);
  dbms_output.put_line(x2.member_id||' '||x2.balance||''||mp||' '||yp);
  end loop; 
  end;
end;

Re: update else insert [message #254989 is a reply to message #254988] Mon, 30 July 2007 06:20 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Do not use this method, look up the syntax for the MERGE statement.
Re: update else insert [message #254990 is a reply to message #254989] Mon, 30 July 2007 06:21 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I am using 8i. I am afriad MERGE is not available there. M I right?

Riaz
Re: update else insert [message #254993 is a reply to message #254990] Mon, 30 July 2007 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Why didn't you give the version in the first post?????? Why???
2/ Why this bunch of IF? And not just "if 'JAN' then ... else ...". From the comment you have only 2 cases: JAN and other.
3/ Try update and if sql%notfound then insert.

Regards
Michel
Re: update else insert [message #255006 is a reply to message #254993] Mon, 30 July 2007 07:00 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I am fetching the values fron one table 'NET' and updating/inserting into the same table 'NET'. Could u be kind enough to give me an example.
Thank you in advance

Riaz
Re: update else insert [message #255015 is a reply to message #255006] Mon, 30 July 2007 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
declare
  cursor c is select empno from emp;
begin
  for x in c loop
    update t set empno=x.empno;
    if sql%notfound then 
      insert into t (empno) values (x.empno);
    end if;
  end loop;
end;
/

Regards
Michel
Re: update else insert [message #255023 is a reply to message #255006] Mon, 30 July 2007 07:44 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
No need for cursors
create or replace procedure condy (mp varchar2, yp number) is
begin
update net n1 set arrears = ( select balance 
                              from net n2 
                              where n2.member_id = n1.member_id
                              and m = to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'Mon')
                              and y = to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'yyyy')
                              )
where m = mp
and y = yp;

insert into net (member_id,arrears,m,y) 
    (select member_id,balance,m,y 
    from net
    where m=to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'Mon') 
    and y=to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'yyyy')
   and member_id NOT IN(select member_id from net where m=mp and y=yp));
  end;

In future, remember to post your version and supply create table and insert scripts

[Updated on: Mon, 30 July 2007 07:46]

Report message to a moderator

Re: update else insert [message #255024 is a reply to message #255015] Mon, 30 July 2007 07:45 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1964/0/ Finally, after this code ends (Michel's code, that is), every single record in a table would have the same EMPNO value.

There's no WHERE clause in the UPDATE statement!

[Updated on: Mon, 30 July 2007 07:46]

Report message to a moderator

Re: update else insert [message #255026 is a reply to message #255015] Mon, 30 July 2007 07:51 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
   declare

   cursor c1 is select member_id,balance,m,y from net
   where m=balance_month and y=balance_year;
   
   begin

for x in c1 loop
  update net set arrears=x.balance where member_id=x.member_id and m=mp and y=yp;
  dbms_output.put_line(x.member_id||' '||x.balance||''||mp||' '||yp);
        if sql%notfound then
              insert into net(member_id,arrears,m,y) values(x.member_id,x.balance,mp,yp);
              dbms_output.put_line(x.member_id||' '||x.balance||''||mp||' '||yp); 
end if;
end loop;

 
END;


and here is the output


SQL> select * from net where member_id=2006100;

MEMBER_ CURRENT_BILL    ARREARS  SURCHARGE         GT       RECD    BALANCE M            Y
------- ------------ ---------- ---------- ---------- ---------- ---------- --- ----------
2006100          265                     0        265                   265 JUN       2007
2006100                    5001                                             MAY       2007


SQL>  exec bbf2('JUN',2007);
Balance Month/YearMAY 2007
2004157 JUN 2007
2006310 1000JUN 2007
2005100 500JUN 2007
2006100 JUN 2007

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from net where member_id=2006100;

MEMBER_ CURRENT_BILL    ARREARS  SURCHARGE         GT       RECD    BALANCE M            Y
------- ------------ ---------- ---------- ---------- ---------- ---------- --- ----------
2006100          265                     0        265                   265 JUN       2007
2006100                    5001                                             MAY       2007




I see no effect...

plz help

Re: update else insert [message #255035 is a reply to message #255024] Mon, 30 July 2007 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Littlefoot, you're right, writing too fast, wanted to write "set ... where empno=x.empno".
Was just an example.

Regards
Michel
Re: update else insert [message #255037 is a reply to message #255026] Mon, 30 July 2007 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't analyze your case, first move your dbms_output BEFORE update, who knows if it doesn't do something in sql and change sql%notfound value.

Regards
Michel
Re: update else insert [message #255180 is a reply to message #255037] Mon, 30 July 2007 23:40 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
  1  create or replace procedure condy (mp varchar2, yp number) is
  2  begin
  3  update net n1 set arrears = ( select balance
  4                                from net n2
  5                                where n2.member_id = n1.member_id
  6                                and m = to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'Mon')
  7                                and y = to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'yyyy')
  8                                )
  9  where m = mp
 10  and y = yp;
 11  insert into net (member_id,arrears,m,y)
 12      (select member_id,balance,m,y
 13      from net
 14      where m=to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'Mon')
 15      and y=to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'yyyy')
 16     and member_id NOT IN(select member_id from net where m=mp and y=yp));
 17*   end;
 18  /

Procedure created.

SQL> select * from net where member_id=2006001;

MEMBER_ CURRENT_BILL    ARREARS  SURCHARGE         GT       RECD    BALANCE M            Y
------- ------------ ---------- ---------- ---------- ---------- ---------- --- ----------
2006001      2943.38          0          0       2945                  2945 JUN       2007

SQL> exec condy('JUN',2007);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from net where member_id=2006100;

MEMBER_ CURRENT_BILL    ARREARS  SURCHARGE         GT       RECD    BALANCE M            Y
------- ------------ ---------- ---------- ---------- ---------- ---------- --- ----------
2006100          265                     0        265                   265 JUN       2007
2006100                    5001                                             MAY       2007




I still am unable to see any effect. Please...
Re: update else insert [message #255245 is a reply to message #255180] Tue, 31 July 2007 02:42 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
supply create table script along with insert scripts for 15 rows of representative data.
You also failed to spot my deliberate error Wink

SQL> select * from net;

 MEMBER_ID M                   Y    BALANCE    ARREARS
---------- ---------- ---------- ---------- ----------
         1 Mar              2007        500          0
         1 Apr              2007        200          0
         1 May              2007        100          0
         2 Mar              2007        500          0
         2 Apr              2007        500          0

SQL>
SQL> create or replace
  2  procedure condy (mp varchar2, yp number) is
  3  begin
  4  update net n1 set arrears = ( select balance
  5                                from net n2
  6                                where n2.member_id = n1.member_id
  7                                and m = to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'Mon')
  8                                and y = to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'yyyy')
  9                                )
 10  where m = mp
 11  and y = yp;
 12
 13  insert into net (member_id,arrears,m,y)
 14      (select member_id,balance,mp,yp
 15      from net
 16      where m=to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'Mon')
 17      and y=to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'yyyy')
 18     and member_id NOT IN(select member_id from net where m=mp and y=yp));
 19    end;
 20  /

Procedure created.

SQL> exec condy('May', 2007);

PL/SQL procedure successfully completed.

SQL>
SQL> select * from net;

 MEMBER_ID M                   Y    BALANCE    ARREARS
---------- ---------- ---------- ---------- ----------
         1 Mar              2007        500          0
         1 Apr              2007        200          0
         1 May              2007        100        200
         2 Mar              2007        500          0
         2 Apr              2007        500          0
         2 May              2007                   500


Oh and 1 other point, bizarrely your first select is for 1 member id, your second select is for someone different. Why?

[Updated on: Tue, 31 July 2007 02:54]

Report message to a moderator

Re: update else insert [message #255262 is a reply to message #255245] Tue, 31 July 2007 04:06 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
the script is as following please

create table net(
member_id varchar2(10),
arrears number(10,8),
balance number(10,8),
m varchar2(3),
y varchar2(4)
)
/
insert into net values(2006001,500,200,'MAY','2007')
/
insert into net values(2006002,1500,200,'MAY','2007')
/
insert into net values(2006003,5020,200,'MAY','2007')
/
insert into net values(2006004,5300,200,'MAY','2007')
/
insert into net values(2006005,5040,200,'MAY','2007')
/
insert into net values(2006006,5050,200,'MAY','2007')
/
insert into net values(2006007,5080,200,'MAY','2007')
/
insert into net values(2006008,5080,200,'MAY','2007')
/
insert into net values(2006009,5070,200,'MAY','2007')
/
insert into net values(2006011,5008,200,'MAY','2007')
/
insert into net values(2006011,8500,200,'MAY','2007')
/
insert into net values(2006012,6500,200,'MAY','2007')
/
insert into net values(2006013,2500,200,'MAY','2007')
/
insert into net values(2006014,1500,200,'MAY','2007')
/
insert into net values(2006015,5300,200,'MAY','2007')
/

insert into net values(2006001,null,200,'JUN','2007')
/
insert into net values(2006002,1200,null,'JUN','2007')
/
insert into net values(2006001,null,200,'JUN','2007')
/
insert into net values(2006002,100,null,'JUN','2007')
/
insert into net values(2006001,null,7200,'JUN','2007')
/
insert into net values(2006002,null,null,'JUN','2007')
/
insert into net values(2006001,null,200,'JUN','2007')
/
insert into net values(2006002,null,null,'JUN','2007')
/
insert into net values(2006001,null,200,'JUN','2007')
/
insert into net values(2006002,100,null,'JUN','2007')
/

[Updated on: Tue, 31 July 2007 04:31] by Moderator

Report message to a moderator

Re: update else insert [message #255420 is a reply to message #255262] Tue, 31 July 2007 16:04 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Hmmm, I have a feeling that your data is inaccurate and not representative. Are you sure that members 2006001 and 2006002 should have so many entries for jun, or did you maybe cut and paste and not edit properly?
Oh yeah, and the code is correct, it's just a problem with the month being in upper case. Fix that, and the code will work.
Now, if you had posted you version, create table and insert scripts along with expected results when you FIRST asked the question, it would have taken a LOT less time for you to get the answer.

[Updated on: Tue, 31 July 2007 16:51]

Report message to a moderator

Re: update else insert [message #255464 is a reply to message #254988] Wed, 01 August 2007 00:26 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Sorry for that

the exact script is as below



create table net
(
member_id varchar2(10),
arrears number(10),
balance number(10),
m varchar2(3),
y varchar2(4)
)
/

insert into net values(2006001,null,2500,'MAY','2007')
/
insert into net values(2006002,null,1200,'MAY','2007')
/
insert into net values(2006003,null,150,'MAY','2007')
/

insert into net values(2006001,null,null,'JUN','2007')
/
insert into net values(2006002,null,null,'JUN','2007')
/



SQL> select * from net;

MEMBER_ID     ARREARS    BALANCE M   Y
---------- ---------- ---------- --- ----
2006001                     2500 MAY 2007
2006002                     1200 MAY 2007
2006003                      150 MAY 2007
2006001                          JUN 2007
2006002                          JUN 2007

SQL> ed
Wrote file afiedt.buf

    create or replace procedure condy   (mp varchar2, yp number) as
        begin
        update net n1 set arrears = ( select balance
                                      from net n2
                                      where n2.member_id = n1.member_id
                                      and m = to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'Mon')
                                      and y = to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'yyyy')
                                      )
        where m = mp
      and y = yp;
      insert into net (member_id,arrears,m,y)
          (select member_id,balance,m,y
          from net
          where m=to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'Mon')
          and y=to_char(add_months(to_date(mp||yp,'Monyyyy'),-1),'yyyy')
         and member_id NOT IN(select member_id from net where m=mp and y=yp));
   commit;
      end;
SQL> /

Procedure created.

SQL> exec condy('JUN',2007);

PL/SQL procedure successfully completed.

SQL>  select * from net;

MEMBER_ID     ARREARS    BALANCE M   Y
---------- ---------- ---------- --- ----
2006001                     2500 MAY 2007
2006002                     1200 MAY 2007
2006003                      150 MAY 2007
2006001                          JUN 2007
2006002                          JUN 2007

SQL> 




I wonder why The BALANCE from MAY could not move to ARREARS in JUN.

Riaz
Re: update else insert [message #255528 is a reply to message #255464] Wed, 01 August 2007 02:13 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:

I wonder why The BALANCE from MAY could not move to ARREARS in JUN.
As I have said, it is a text CASE issue. All you need to do is solve that and the code will work.

Just to show that it does actually work:
truncate table net succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
MEMBER_ID  ARREARS                BALANCE                M   Y    
---------- ---------------------- ---------------------- --- ---- 
2006001                           2500                   MAY 2007 
2006002                           1200                   MAY 2007 
2006003                           150                    MAY 2007 
2006001                                                  JUN 2007 
2006002                                                  JUN 2007 

5 rows selected

anonymous block completed
MEMBER_ID  ARREARS                BALANCE                M   Y    
---------- ---------------------- ---------------------- --- ---- 
2006001                           2500                   MAY 2007 
2006002                           1200                   MAY 2007 
2006003                           150                    MAY 2007 
2006001    2500                                          JUN 2007 
2006002    1200                                          JUN 2007 
2006003    150                                           JUN 2007 

6 rows selected

But I need you to put the effort in to try to understand the code and adapt it as necessary.
Previous Topic: Rank function (merged)
Next Topic: to_number and ORA-01722 (really interesting)
Goto Forum:
  


Current Time: Sat Dec 10 18:29:54 CST 2016

Total time taken to generate the page: 0.10330 seconds