Solved

PL/SQL to update multiple rows in one table using data from another table in the same schema

Posted on 2004-08-31
16
6,294 Views
Last Modified: 2007-12-19
I have two tables in the schema.

The first table, violation_codes, has a section_number and fine, among other columns, but these two are the focus of the problem.

The second table, bond, has an orc and fine, among other columns as well.

For each row in the violation_codes table where the section_number equals the orc in the bond table, I need to update the violation_codes.fine column with the fine amount in the bond table.

I have been playing around with a cursor to accomplish this, but haven't had any luck.  Any help would be appreciated.
0
Comment
Question by:dkblinux98
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 11947522
You should be able to do this in a simple update statement.

update violation_codes vc set vc.fine = ( select b.fine from bond b where b.orc = vc.section_number )
where vc.section_number in ( select orc from bond );
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11947600
update violation_codes vc set vc.fine = ( select b.fine from bond b where b.orc = vc.section_number )
where exists ( select 1 from bond b where orc = vc.section_number );
0
 

Author Comment

by:dkblinux98
ID: 11952339
I had attempted such an update statement before I posted the question.  It generates the error...ORA-01427: single-row subquery returns more than one row.  And the error makes sense because I'm wanting to update 424 rows of data in the violation_codes table with 424 rows of data in the bond table.  For example:

violation_codes                     bond
fine      section_number       fine     orc
--------------------------        -------------
100        4511.26                 35      4511.26
100         4511.27                35      4511.27

and so forth.

There are rows in the violation_codes table which will not be updated.  And I can't add the rows because there are constraints on the violation_codes tables and I need the new data from the bond table to update the information in the existing rows of the violation_codes table.

So I know I need a PL/SQL program to do this which will loop through each valid row of the bond table and update the corresponding row in the violation_codes table.  But thus far, I haven't been successful.  I know next to nothing about PL/SQL.

Here is the code of my last attempt if it helps.  I'm sure it will reveal my lack of  PL/SQL knowledge.
  1  declare
  2  cursor bond1 is
  3  select
  4  violation_codes.section_number,
  5  violation_codes.fine,
  6  bond.orc
  7  into section,oldfine,bondorc
  8  from violation_codes,bond
  9  where violation_codes.section_number=bond.orc;
 10  section number := 0;
 11  newfine number := 0;
 12  rcnt number := 424;
 13  begin
 14  for i in bond1
 15  loop
 16     begin
 17         select fine, count(*)
 18         into newfine,cnt
 19         from bond
 20         where bondorc = section
 21         having count(*) = rcnt;
 22         update violation_codes
 23         set oldfine = newfine
 24         where bondorc = section;
 25     end;
 26     newfine := 0;
 27  end loop;
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11952710
update violation_codes vc set vc.fine = ( select b.fine from bond b where b.orc = vc.section_number )
where exists ( select 1 from bond b where orc = vc.section_number );

This statement will fail if ( select b.fine from bond b where b.orc = vc.section_number ) returns more than one row.

Your job is to define the business logic to choose which of those rows is the applicable one.

In the above logic you need to use ROWNUM rather than COUNT.  I suspect you need an order by clause to get the correct fine.
0
 

Author Comment

by:dkblinux98
ID: 11952872
I actually need to update 424 rows.  Basically I'm updating the violation_codes table with new fine information from the bond table where the fine is correct.  There are 424 violation codes for which I need to update the fine.  The fine isn't the same for each of the 424 rows.  I will keep playing with the PL/SQL.
0
 

Author Comment

by:dkblinux98
ID: 11957542
Okay, here is the procedure I wrote to do the job.  It runs, but is taking a very long time.  It would be better to have a cleaner process and possibly some feedback, but with my limited PL/SQL understanding, I'm stretching my limits at the moment.

declare
newfine number;
orc varchar2(15);
section varchar2(15);
cursor mybond is
select bond.fine, bond.orc, violation_codes.section_number
from bond,violation_codes
where bond.orc = violation_codes.section_number;
begin
open mybond;
fetch mybond into newfine, orc, section;
while mybond%found loop
update violation_codes set fine=newfine;
end loop;
end;
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11958601
program logic is way off.  it is setting all rows of violation_codes.fine to a single value.

need where clause at the very least.

update violation_code set fine = newfine where section_number = orc.

You don't need to retrieve orc and section_number because they are the same !


0
 

Author Comment

by:dkblinux98
ID: 11968460
Sorry, I hadn't finished the statement when I pasted it in.  Here is the one that ran, but it still doesn't seem to be setting the fine correctly.

declare
newfine number;
orc varchar2(15);
section varchar2(15);
cnt number;
cursor mybond is
select bond.fine, bond.orc, violation_codes.section_number
from bond,violation_codes
where bond.orc = violation_codes.section_number
and bond.fine<>violation_codes.fine;
begin
cnt := 0;
open mybond;
for cnt in 1 .. 510 loop
fetch mybond into newfine, orc, section;
update violation_codes set fine=newfine
where orc=section and fine<>newfine;
end loop;
end;

It still seems to be updating each row of the violation_codes table with the first fine it finds.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:earth man2
ID: 11968991
The code is doing exactly what you have programmed it to do.

You need to stand back from this problem and look at the data.

Consider this sql script.

select b.fine from bond b where b.orc = &a_section_nuimber;

For some values of "a_section_number" it will return more than one row.

Therefore YOU must define the logic to choose which is the correct row wtih which to update the violation_codes table.

WHERE IN THE pl/sql above ARE YOU DOING THAT ?
0
 

Author Comment

by:dkblinux98
ID: 11969153
For each row that the sql script returns there is one value for fine in bond that needs to update the corresponding row in violation_codes.
In other words, for the 510 rows in question there is a one-to-one relationship between the two tables.

What I want to do is for each row that the script retrieves, run the update script.  If this were a unix script I would do a rather simple for loop in a shell script and it would retrieve and update each row, row by row, until it gets to the end of the data stream.

My frustration is that I don't know PL/SQL at all so I don't know exactly what the script is doing.  But it seems that it is only fetching the newfine value once and then running the update script multiple times.  I've tried numerous versions of the script, too numerous to post, but I still come up with the same result.  I know what I need it to do, I just have no idea how to code it.  I've searched the internet, but with no luck.  I feel good that I , at least, wrote a script that runs.  That's a start I suppose.  And everyone has to start somewhere.

With my limited understanding, what I thought would happen in the code is that for each row the script retrieves it would 1) Retrieve a row 2) store the information from the retrieval into the variables 3) apply the retrieved information with the update script 4) start back at the beginning and do it all over again 510 times.  Obviously it isn't doing that.  But a similar shell script (in appropriate syntax) would do just that.
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 11974359
The update statement written by earthman2 should work fine, if your claim (of having 1 to 1 relation between the two tables) is right.

As earthman has mentioned in his post above - {http:#11952710} ,

If you have unique orc in bond table, and unique section_number in violation_codes table, there should'nt be any problems.

If you just had data like (as you mentioned in your post above) :
violation_codes                     bond
fine      section_number       fine     orc
--------------------------        -------------
100        4511.26                 35      4511.26
100         4511.27                35      4511.27

Then, after the update statement from earthman2, the result should be :
violation_codes                     bond
fine      section_number       fine     orc
--------------------------        -------------
100        4511.26                 100      4511.26
100         4511.27                100      4511.27

But, you will get the problem, if you had the following records in your table :
violation_codes                     bond
fine      section_number       fine     orc
--------------------------        -------------
100        4511.26                 35      4511.26
100         4511.27                35      4511.26 --> Duplicate orc

Anyways, you have been asking for a PLSQL till now so here is a small PL/SQL block which will do the same :

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
v_fine    NUMBER;

CURSOR c1 is
SELECT fine, section_number
FROM violation_codes FOR UPDATE;

BEGIN
FOR c1_row in C1
LOOP
    -- Now you would like to fetch the latest fine from bond table where orc, is same
    -- as section_number that has been fetched from violation_codes
    BEGIN
        SELECT fine
        INTO v_fine
        FROM bond
        WHERE orc = c1_row.section_number;
    EXCEPTION
        WHEN TOO_MANY_ROWS THEN
        -- Most probably here is your problem, and you should get this error
        DBMS_OUTPUT.PUT_LINE('Recd TOO_MANY_ROWS for row ' || c1_row.fine || ',' || c1_row.section_number);
    END;

    -- If you reach this point, you have successfully fetched the fine from bond table,
    -- so now update the value as per your convenience

    UPDATE violation_codes
    SET fine = v_fine
    WHERE section_number = c1_row.section_number;

    -- Just for info, show the number of records updated by the above update
    DBMS_OUTPUT.PUT_LINE('No of records updated for section number ' || c1_row.section_number || ' is ' || SQL%ROWCOUNT);
END LOOP;
END;


update violation_codes vc set vc.fine = ( select b.fine from bond b where b.orc = vc.section_number )
where exists ( select 1 from bond b where orc = vc.section_number );

This statement will fail if ( select b.fine from bond b where b.orc = vc.section_number ) returns more than one row.

Your job is to define the business logic to choose which of those rows is the applicable one.

In the above logic you need to use ROWNUM rather than COUNT.  I suspect you need an order by clause to get the correct fine.
0
 
LVL 9

Accepted Solution

by:
pratikroy earned 125 total points
ID: 11974387
oops ...

If you just had data like (as you mentioned in your post above) :
violation_codes                     bond
fine      section_number       fine     orc
--------------------------        -------------
100        4511.26                 35      4511.26
100         4511.27                35      4511.27

Then, after the update statement from earthman2, the result should be :
violation_codes                     bond
fine      section_number       fine     orc
--------------------------        -------------
35        4511.26                 35      4511.26
35         4511.27                35      4511.27

But, you will get the problem, if you had the following records in your table :
violation_codes                     bond
fine      section_number       fine     orc
--------------------------        -------------
100        4511.26                 35      4511.26
100         4511.27                35      4511.26 --> Duplicate orc
0
 

Author Comment

by:dkblinux98
ID: 12006510
I will give the pl/sql a try.  I know that the original update script would work if the fine amount was constant.  An even more simple update script would work if that was the case.  The fine amount is potentially different for each combination of orc,fine and section_number, fine and that is why I felt I needed a pl/sql script and the update script wouldn't work.  I had already ran the original update script and several attempts at pl/sql scripts before I posted.  There is definately no duplicate orc codes in the bond file.
0
 

Author Comment

by:dkblinux98
ID: 12007523
With some slight modification, here is the pl/sql script that worked:

DECLARE
v_fine    NUMBER;

CURSOR c1 is
SELECT
section_number,fine
FROM
violation_codes
WHERE
section_number IN
(select
violation_codes.section_number
from
violation_codes,bond
where
bond.orc=violation_codes.section_number
FOR UPDATE;

BEGIN
FOR c1_row in C1
LOOP
    -- Now you would like to fetch the latest fine from bond table where orc, is same
    -- as section_number that has been fetched from violation_codes
    BEGIN
        SELECT fine
        INTO v_fine
        FROM bond
        WHERE orc = c1_row.section_number;
    EXCEPTION
        WHEN TOO_MANY_ROWS THEN
        -- Most probably here is your problem, and you should get this error
        DBMS_OUTPUT.PUT_LINE('Recd TOO_MANY_ROWS for row ' || c1_row.fine || ',' || c1_row.section_number);
    END;

    -- If you reach this point, you have successfully fetched the fine from bond table,
    -- so now update the value as per your convenience

    UPDATE violation_codes
    SET fine = v_fine
    WHERE section_number = c1_row.section_number;

    -- Just for info, show the number of records updated by the above update
    DBMS_OUTPUT.PUT_LINE('No of records updated for section number ' || c1_row.section_number || ' is ' || SQL%ROWCOUNT);
END LOOP;
END;
0
 

Author Comment

by:dkblinux98
ID: 12007562
oops...the closing paren was missing in the previous post from the paste in the line before FOR UPDATE
0
 
LVL 13

Expert Comment

by:PillalamarriVenkateswaraRao
ID: 22509709
why dont we do it like this

table sms_installed_products
----------------------------------------
Installed_Sno         number(5)
DL_No                     number(5)

 update sms_installed_products set DL_No = case
      when    Installed_Sno = 3 then 3*2
     when    Installed_Sno = 4 then 4*3
     when    Installed_Sno = 5 then 5*4
     end where installed_sno in (3,4,5)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now