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

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.

score 0 · Answer 1 · answered Sep 13 '17 at 10:57

In your update query,as it is a for loop hence last value of the acct_num from the table1 loop gets updated to all the rows from table2.

There must be a common column to join between table1 and table2 use it in the where condition.

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

This additional filter in where clause can solve ur query.

Update specific rows in a column

1 Answers1