Solved

plsql looping question

Posted on 2014-04-09
8
292 Views
Last Modified: 2014-04-10
Hi,

I want to take values from a column in one table and put them into a column in another table. There is no relationship between the tables so no join can be done. I just want to go one by one through the values in table1 and put them into particular cells (that contain a value of 9999) of another column in another table. I was thinking of some kind of loop like below but not sure exactly how to go about that.

You'd have to get all the values of table1 and loop through them, and then find the first  instance of col2 where col2 = 9999 and then put the first value of col1 in, then put the second value of col1 into the second instance of col2 where col2 = 9999 and so on.

what's the best way to do that?

BEGIN

    FOR val IN (SELECT col1 FROM table1) 
        LOOP
                  UPDATE table2 
                  SET col2 = val
                  WHERE col2 = 9999;
                  
    END LOOP;

END;

Open in new window

0
Comment
Question by:AidenA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 39988598
That wouldn't work because your first update will update all records where col2 = 9999.

I would try something like this.  It is the first way that I came up with.

DECLARE 
    rid ROWID; 
BEGIN 
    FOR val IN (SELECT col1 
                FROM   table1) LOOP 
        BEGIN 
            SELECT Min(ROWID) 
            INTO   rid 
            FROM   table2 
            WHERE  col2 = 9999; 
        EXCEPTION 
            WHEN no_data_found THEN 
              EXIT; 
        END; 

        UPDATE table2 
        SET    col2 = val 
        WHERE  ROWID = rid; 
    END LOOP; 
END; 

Open in new window


This should find the first row with 9999 and then update it.  If no rows are found with 9999 (i.e. it runs out of rows in table2), then the loop will just exit.  Not sure what you wanted to do in that case.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 39988610
Try something like this (Plain SQL)

MERGE INTO table2 x USING
(
  SELECT col1, ri FROM
  (SELECT col1, ROWNUM rn FROM table1 ORDER BY col1) t1,
  (SELECT col2, ROWNUM rn, ROWID ri FROM table2 WHERE col2 = 9999) t2
  WHERE t1.rn = t2.rn
) y
ON( x.ROWID = y.ri)
WHEN MATCHED THEN
UPDATE
SET x.col2 = y.col1
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 39988629
Your case is very unusual, but if that's what you want...

What if you have more records in table1 than records in table2?
Do you have a date column to go by? (see the following example if you want to update based on the latest records or perhaps max values)


MERGE INTO table2 x USING
(
  SELECT col1, ri FROM
  (SELECT col1, row_number() over(order by rowid desc) rn FROM table1) t1,
  (SELECT col2, ROWNUM rn, ROWID ri FROM table2 WHERE col2 = 9999) t2
  WHERE t1.rn = t2.rn
) y
ON( x.ROWID = y.ri)
WHEN MATCHED THEN
UPDATE
SET x.col2 = y.col1
0
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39989954
With no relationship between the tables what is the purpose?

Also without an 'order by' on the select you are getting the values in ANY order the database decides to return them.

If all you want to to do is randomly assign the values in one table with values from another, there are probably better ways.

There almost has to be some specific requirement other than 'random'.

Can you provide sample data and expected results?
0
 

Author Comment

by:AidenA
ID: 39991760
yes it's a strange case, there is supposed to be a proper key in that column at some stage, but currently there isn't so I've just had to put random numbers in there, and now I want to put those random numbers into another table to create a foreign key relationship (don't ask :-/   )

So, I was trying your example johnsone, but I was getting an error on the 'SET    col2 = val' where it was saying that the 'ora-06550: pls-00382expression is of the wrong type) . I've just checked and both seem to be NUMBER(12) to me so I'm not sure what might be causing that?

i'll check your method now paquicuba
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 39991771
Sorry, missed the variable name.

DECLARE 
    rid ROWID; 
BEGIN 
    FOR val IN (SELECT col1 
                FROM   table1) LOOP 
        BEGIN 
            SELECT Min(ROWID) 
            INTO   rid 
            FROM   table2 
            WHERE  col2 = 9999; 
        EXCEPTION 
            WHEN no_data_found THEN 
              EXIT; 
        END; 

        UPDATE table2 
        SET    col2 = val.col1 
        WHERE  ROWID = rid; 
    END LOOP; 
END; 

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39991793
What determines "the first  instance of col2 where col2 = 9999" and "the first value of col1"? Are there an equal number of col1 values in table1 and col2 values of 9999 in table2? slightwv's suggestion of sample data and expected results would be a great help in understanding your intent.
0
 

Author Comment

by:AidenA
ID: 39992041
yeah that seemed to work fine thanks johnsone

for the other questions, the tables were of different size so johnsone's method worked best there, don't think sample data was needed... just wanted to move data from one col to another in a different table without a join, so what the data 'is' is irrelevant really

but thanks for all your help on that!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

690 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