I have a column with 82 rows(from table1) and I want to update just the 76 rows in it with different values from another table(table2). Avoiding 6 specific rows(1, 10, 38, 39, 46, 77). Leaving these rows with null values.

I used cursor to save all the 76 rows from my table2

Cursor my_cur
  Select * from table2

Then I tried to update my table1

 For x in  my_cur loop
   Update table1
   Set my_col = x.acct_num
   Where rnum not in ('1', '10', '38', '39', '46', '77');
end loop;

But I ended up updating these rows with same value(only 1 acct_num to all 76 rows). It seems that the last value(acct_num) from my cursor was the only one im always getting.

I want to enter different acct_num to 76 rows and in the same order as in my table2

I added rownum in both of them to have at least be sure what rows to skip. Any ideas how to do it? Thanks.

  • What is the key in table1 and table2? Aug 16, 2017 at 2:00
  • @Lennart there's no actually a key for them. I just added rownum for them to have a connection
    – Blank
    Aug 16, 2017 at 2:01

2 Answers 2


you are missing a key based on what you can update the data ... obviously there is always the last value in table1.my_col because what you are doing is looping through all values and updating the column in the table1..

The database design is not correct .. Can you share your table structure / definition ?

Then, we can come up with some solution for you

EDIT: if you wanna just move those records from one table to another ...

try this:

insert into t1 (my_col) as select acct_num from t2 where rownum not in ('1', '10','38', '39', '46', '77');
  • Hi, thanks for the reply. The tables dont actually have a key in the first place. I just added rownum for me to have at least connected the two tables. All i need to do is to enter all 76 data from my table2 to table1 avoiding those 6 rows.
    – Blank
    Aug 16, 2017 at 3:19
  • how about insert into t1 (my_col) as select acct_num from t2 where rownum not in ('1', '10', '38', '39', '46', '77'); ??
    – Mr.P
    Aug 16, 2017 at 3:21
  • I got an error, "missing values keyword"
    – Blank
    Aug 17, 2017 at 2:30
  • please share your table structure ..
    – Mr.P
    Aug 17, 2017 at 3:17
  • Sorry, what you mean by table structure? I just have two tables with different number of rows and I want to enter values to the other table avoiding specific rows.
    – Blank
    Aug 18, 2017 at 3:31

Assuming you have a rownum column that relates rows in t1 and t2 you can use a merge statement (untested since we don't know the structures of the tables):

) as y
    ON x.rownum = y.rownum 
WHEN MATCHED AND x.rownum NOT IN ('1', '10', '38', '39', '46', '77') THEN
    UPDATE SET x.my_col = y.acct_num;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.