plsql looping question

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

AidenAAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
paquicubaCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
paquicubaCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
AidenAAuthor Commented:
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
 
awking00Commented:
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
 
AidenAAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.