CODEWITHSUNDEEP
mysql
Sai
Sai

Reputation: 1

Is it possible to insert a row using the INSERT function on a queried table?

INSERT INTO (
    SELECT student_info.Student_Name,scores.Final
    FROM student_info 
    INNER JOIN scores ON scores.Student_id=student_info.Student_id 
        AND scores.Subject_id=1)
(Student_Name,Final) VALUES("a",1)

Something like this.....What i want to achieve is that i want to add a new row to the queried result which will display the average of the columns above it.

Upvotes: 0

Views: 38

Answers (2)

Barmar
Barmar

Reputation: 781592

Use UNION to combine the results of two queries into a single result:

SELECT student_info.Student_Name,scores.Final
FROM student_info 
INNER JOIN scores ON scores.Student_id=student_info.Student_id 
    AND scores.Subject_id=1

UNION

SELECT "a", AVG(scores.Final)
FROM student_info 
INNER JOIN scores ON scores.Student_id=student_info.Student_id 
    AND scores.Subject_id=1

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

if you need an insert select you could use this way 

INSERT INTO your_table_name  (Student_Name,Final)
SELECT student_info.Student_Name,scores.Final
FROM student_info 
INNER JOIN scores ON scores.Student_id=student_info.Student_id 
AND scores.Subject_id=1

and eventually if need a added row you can add an union clause with the literal value to your select 

INSERT INTO your_table_name   (Student_Name,Final)
SELECT student_info.Student_Name,scores.Final
FROM student_info 
INNER JOIN scores ON scores.Student_id=student_info.Student_id 
AND scores.Subject_id=1
union all  
select "a", 1
from dual;

Upvotes: 0

Related Questions