CODEWITHSUNDEEP
mysqlsubquery
Xofraz
Xofraz

Reputation: 56

mysql update or insert to another table using subquery that returns x amount of rows

I have been trying for about 8 hours now and to no avail. I am trying to update or insert the AVG of 2 to 10 rows from a single column into another table.

The code I use right now to calculate the average.

SELECT COUNT(*), NAME, AVG(AVERAGE) FROM table1 GROUP BY NAME ORDER BY NAME;

This returns the proper results as I used group by as a workaround but now I need to get that data into another table.

However when I try to run update or insert mysql always complains about a multi line subquery return.

How can I get this into table2 where the name matches and into column average of table2? The name is 100% unique. I also would like the names to match. This will be updated maybe once a day.

EDIT: First table ENTRY_ID, NAME(not unique for multiple entries), AVERAGE Second table has ID(unique), NAME(unique), AVERAGE

I have tried so many queries its not funny I've searched all the documentation I'm just not putting something together right.

When you run the above it might have 8 rows it runs AVG(AVERAGE) maybe 6 they are matched by name.

I want to store them in table2 where the name in table1 matches the name in table2 but just gives the single average of the above query.

Thanks

EDIT2: This is the code that worked

INSERT INTO table2 (NAME, AVERAGE) (SELECT * FROM (SELECT NAME, AVG(AVERAGE) as AVERAGE FROM table1 GROUP BY NAME) main_query) ON DUPLICATE KEY UPDATE table2.AVERAGE = main_query.AVERAGE;

Thanks to @ARubiksCube

Upvotes: 2

Views: 228

Answers (1)

ARubiksCube
ARubiksCube

Reputation: 156

I am unsure of what your actual error message is. My guess is that you are using a newer version of MYSQL so you are getting this error.

This query will update all the records in table2 with the values from table1. If the record does not exist, it will be inserted. If the record exists, It will be updated.

INSERT INTO table2 (NAME, AVERAGE)
SELECT *
FROM
(SELECT NAME, AVG(AVERAGE) as AVERAGE
FROM table1
GROUP BY NAME) main_query
ON DUPLICATE KEY
UPDATE table2.AVERAGE = main_query.AVERAGE;

Upvotes: 1

Related Questions