How to assign value of SQL query to a MySQL table column

Question

I have two MySQL tables, g734 and g8internal. I ran an SQL SELECT query given below in which I would like to put the result of the query to the Maths field and Total_Score field in table g8internal. The fields in g734 and g8internal are uniform. They are StudentID, FirstName, LastName, Maths and Total_Score. Please have a look at my SQL code below. The one I have is giving SQL syntax error in phpMyAdmin. I would like to store the result of the query to Total_Score and Maths in g8internal.

SELECT StudentID, FirstName, LastName, SUM(Total_Score) AS T_Score, SUM(Maths) AS Mth
FROM
(
SELECT StudentID, FirstName, LastName, Total_Score, Maths FROM g734
   UNION ALL
SELECT StudentID, FirstName, LastName, Total_Score, Maths FROM g8internal
) t on g8internal.FirstName = g734.FirstName AND g8internal.LastName = g734.LastName
GROUP BY StudentID, FirstName, LastName
SET g8internal.Total_Score = T_Score,
g8internal.Maths = Mth

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Apr 11 '21 at 07:47
It seems fantastically unlikely that a column called 'maths' would be stored alongside columns called 'firstname' and 'lastname' in a normalised environment — Strawberry, Apr 11 '21 at 07:49

score 0 · Answer 1 · answered Apr 17 '21 at 17:57

Try the query like this and see if it works. And i dont think you need to join with FirstName and LastName if Student Id works as key.

update 
g8internal g8int 
join 
(
    select
      t.StudentID, 
      t.FirstName, 
      t.LastName, 
      SUM(t.Total_Score) AS T_Score, 
      SUM(t.Maths) AS Mth 
    from  
      (

        SELECT 
          StudentID, 
          FirstName, 
          LastName, 
          Total_Score, 
          Maths 
        FROM 
          g734 

        UNION ALL 

        SELECT 
          StudentID, 
          FirstName, 
          LastName, 
          Total_Score, 
          Maths 
        FROM 
          g8internal
      ) t 
    group by
      t.StudentID, 
      t.FirstName, 
      t.LastName 
) t1 on g8int.StudentID = t1.StudentID 
          and g8int.FirstName = t1.FirstName 
          and g8int.LastName = t1.LastName 
set  
    g8int.Total_Score = t1.T_Score, 
    g8int.Maths = t1.Mth

Reference : MySQL - UPDATE query based on SELECT Query

Thanks

How to assign value of SQL query to a MySQL table column

1 Answers1