0

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.

  • What is the key in table1 and table2? – Lennart Aug 16 '17 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 '17 at 2:01
0

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 '17 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 '17 at 3:21
  • I got an error, "missing values keyword" – Blank Aug 17 '17 at 2:30
  • please share your table structure .. – Mr.P Aug 17 '17 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 '17 at 3:31
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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