0

I was just trying to copy the values of one column into an empty column of another table . The two tables are students and payments . Students have two columns :

  • rno - primary key of number type // i filled it with some entries

  • amount //this is completely empty

payments also have same no of columns :

  • rno - foreign key referencing rno of students table

  • amount //this is FILLED

Now to copy amounts column from payments to students , i tried this command

insert into students(amount) select amount from payments ;

now normally this command works like a charm ,but here it is behaving slightly different . It throws an error that NULL values cannot be inserted into students.rno

I tried reasoning that maybe its due to different number of entries inserted in two tables , but on eqalizing the no . of entries in both the tables , just the same result .

So the question is that how can i copy in such a situation ?

HalfWebDev
  • 7,022
  • 12
  • 65
  • 103
  • Did you mean to insert just amounts or did you want a `rno` there so that the data is meaningful? – Ben Feb 26 '12 at 21:20

2 Answers2

2

Not quite clear on your requirements, but this will populate the STUDENTS table with the SUM of matching payments in the PAYMENTS table. Is this what you're after?

UPDATE STUDENTS
SET    AMOUNT = (SELECT SUM(PAYMENTS.AMOUNTS)
                 FROM   PAYMENTS
                 WHERE  PAYMENTS.RNO = STUDENTS.RNO);
cagcowboy
  • 30,012
  • 11
  • 69
  • 93
1

You don't want to add records to the students table (which is what INSERT does) you want to UPDATE existing records.

I am not very familiar with Oracle syntax, but I adapted the answer to this question Update statement with inner join on Oracle to hopefully meet your needs.

UPDATE students
SET    students.amount = (SELECT payments.amount
                          FROM   payments
                          WHERE  students.rno = payments.rno)
Community
  • 1
  • 1
gangreen
  • 849
  • 7
  • 9
  • Note that this will error if more than 1 payment for a student. – cagcowboy Feb 26 '12 at 21:33
  • Oh, good point. I assumed the relationship was 1:1 for some reason. P.S., Thanks for the edit, I noticed afterword it was rno not mo ;) – gangreen Feb 26 '12 at 21:38
  • @cagcowboy Also, that's interesting that this would throw an error. If I'm not mistaken, in SQL Server the "last" match would be the value that is used in the UPDATE. I like Oracle's take on this better. – gangreen Feb 26 '12 at 21:42