Is there any simple way in MySQL to insert values in a table that are fetched from another table

Question

I have three database tables

    Graduate_survey
    id(pk)AI  PO1  PO2  PO3 

    Alumni_survey
    id(pk)AI  PO1  PO2  PO3 

    faculty_survey
    id(pk)AI  PO1  PO2  PO3

after inserting the values into these tables and calculating the average by union of three tables now i have to store the average value in another table total_survey

total_survey id(pk)AI PO PO1 PO2 My codes are

               <?php 
                @include("connection.php");
               if($_POST['submit'])
                 {
                 $sql= "SELECT t.id
               , AVG(t.PO1) AS total_PO1, AVG(t.PO2) AS total_PO2, 
                 AVG(t.PO3) AS total_PO3
                 FROM ( SELECT Graduate_survey.id
                , Graduate_survey.PO1,  Graduate_survey.PO2,  
                 Graduate_survey.PO3
                FROM Graduate_survey
                 UNION 
                 SELECT alumni_survey.id
                 , alumni_survey.PO1, alumni_survey.PO2, alumni_survey.PO3
                FROM alumni_survey
                 UNION
                 SELECT faculty_survey.id
                , faculty_survey.PO1, faculty_survey.PO2, faculty_survey.PO3
                 FROM faculty_survey
                 )t
                GROUP BY t.id 
                ORDER BY t.id";
               $rData=mysql_query($sql);
               $res=mysql_fetch_array($rData);
               $sql="insert into total_survey 
              values('','total_PO1','total_PO2','total_PO3')";
               mysql_query($sql);

               }
               ?>
              <form method="post">
              <p align="center"><input type="submit" name="submit" 
              value="Click here to calculate the final indirect assesment"> 
              </p>
               </form>

select query is working properly but insert query is not working. The problem is i have to insert the datas that are shown by the select query in another table total_survey. How to store the fetched data in another table in MySQL?

https://stackoverflow.com/questions/5391344/insert-with-select — shudder, May 23 '17 at 21:38
A `trigger` comes to mind - https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html — Professor Abronsius, May 23 '17 at 22:34

score 1 · Accepted Answer · answered May 23 '17 at 21:47

1

You will need to replace some of my generic naming, but below is what you're after

INSERT INTO `SchemaName`.total_survey (`ColumnName1`, `ColumnName2`, `ColumnName3`, `ColumnName4`)

SELECT t.id, AVG(t.PO1) AS total_PO1, AVG(t.PO2) AS total_PO2, AVG(t.PO3) AS total_PO3
FROM ( SELECT Graduate_survey.id, Graduate_survey.PO1, Graduate_survey.PO2, Graduate_survey.PO3
        FROM Graduate_survey

        UNION 

        SELECT alumni_survey.id, alumni_survey.PO1, alumni_survey.PO2, alumni_survey.PO3
        FROM alumni_survey

        UNION

        SELECT faculty_survey.id, faculty_survey.PO1, faculty_survey.PO2, faculty_survey.PO3
        FROM faculty_survey
 )t
GROUP BY t.id 
ORDER BY t.id

answered May 23 '17 at 21:47

DarbyM

1,173
2
9
25

it is a best practice type of thing.. only become required in certain scenarios where you are working with identical named tables that exists in different Schemas. – DarbyM May 23 '17 at 22:19
SchemaName/DataBase Name is the Name of the schema that your total_survey table resides in.. – DarbyM May 23 '17 at 22:24
There are no MySQL syntax errors in the above insert statement. Though YOU do need to know the column names within table total_survey so that you can replace the generic column names such as ColumnNamen1, ect... Your original post does not list these, so I could not include them in my example – DarbyM May 23 '17 at 22:31
it works...some typical mistakes was done by me now its working..thank you – Debasish Choudhury May 23 '17 at 22:37

Is there any simple way in MySQL to insert values in a table that are fetched from another table

1 Answers1