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.
Best Answer
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: