Update specific rows in a column

Question

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
 Is
  Select * from table2

Then I tried to update my table1

Begin
 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;
End;

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.

Answer 1

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');

Answer 2

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):

MERGE INTO T1 as x
USING (
    SELECT * FROM T2
) 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;