CODEWITHSUNDEEP
sqloracle-databaseinsertoracle11g
HalfWebDev
HalfWebDev

Reputation: 7638

Insert by select statement -oracle

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 :

payments also have same no of columns :

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 ?

Upvotes: 0

Views: 563

Answers (2)

gangreen
gangreen

Reputation: 909

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)

Upvotes: 1

cagcowboy
cagcowboy

Reputation: 30828

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

Upvotes: 2

Related Questions