-3

test1

t1  t2

1

test2

x

1

2

3

upto 20

so I want all the x values copy into t2.
How should I?

More detailed :

There r 2 tables 'test1' , 'test2'. ' test1 ' having 2 columns i.e ' t1 ' , ' t2 ' and ' test2 ' having 1 column i.e ' x '. so I need to copy test2.x column values to test1.t2 column. test1.t1 - 1, test1.t2 - null, test2.x - 1,2,3,4 ..... 20

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Ranjan
  • 1
  • 3

1 Answers1

2

Question text is not clear, as much as i understand, you have values for t1 column of test1 table as 1,2,3 ... 20, also, and if you'd like to copy all from test2.x, you may use :

update test1 t1
   set t1.t2 = ( select t2.x
                   from test2 t2
                  where t2.x = t1.t1 );

but, actually there's no need of another table(table2)

update test1 t1 set t1.t2 = t1.t2;

With respect to your last edit, you need a merge statement :

merge into test1 a
  using test2 b
    on (nvl(a.t1,0) = nvl(b.x,0))
  when matched then
    update set a.t2 = nvl(b.x,0)
  when not matched then
    insert(t2)
    values(nvl(b.x,0));

and then you can get the following :

select * from test1 order by t2;

T1  T2
--  --------------------------------------------------
1   1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    .
    .
    .
    20

demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Thanks for ur answer, but I have tried this, it will update only one row of the test1 table, I want 1,2,3 ..... 20 to be there in the test1.t2 column. Hope u understand my question. I need test2.x values copy to test1.t2 column. – Ranjan Apr 07 '18 at 13:13
  • @Ranjan you're welcome, friend. I've edited both your question(according to your comment), and my answer. – Barbaros Özhan Apr 07 '18 at 17:43
  • thank you @Barbaros Ozhan, I am appreciating ur answer, but what if I need the values should exactly like test2.x, Not side by side, it should by row by row. Like ur 1st answer. it was ok, but it only updates 1 row, and I need all the 20 rows in 'test1' table. – Ranjan Apr 08 '18 at 10:50
  • update (select * from test1,test2) set t2=x; I got the error in this query.(ORA-01779: cannot modify a column which maps to a non key-preserved table.) – Ranjan Apr 08 '18 at 10:56
  • @Ranjan I edited the answer so that you can see the results in the Demo. – Barbaros Özhan Apr 08 '18 at 12:59
  • Ozhan, Plz see the above, I want to get this as a result. – Ranjan Apr 08 '18 at 18:10
  • Ozhan, Thank you so much friend, now I got it. – Ranjan Apr 10 '18 at 11:09