Solved

plsql looping question

Posted on 2014-04-09
8
286 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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 34

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
 
LVL 76

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 34

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 31

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

757 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

18 Experts available now in Live!

Get 1:1 Help Now