1

I have recently started learning MySQL and am trying to join data from two tables into a third table built.

The two tables are:

'Company Financials' - Column of 'Revenue'

'Fortune500' - Columns of 'Year' and 'Company'

My query code is:

INSERT INTO Company(ID, Year, Company, Revenue) (select Year, Company, Revenue from (select Revenue from Company_Financials union select Year, Company from Fortune500));

Fortune 500 Table:

Fortune500 Table

Company Financials Table:

Company Financials table

I've had multiple errors related to this code, but have been able to figure out why they were wrong. This one is stumping me :(

Any help is much appreciated!

4
  • Each derived table needs alias asigned: INSERT INTO Company(ID, Year, Company, Revenue) (select Year, Company, Revenue from (select Revenue from Company_Financials union select Year, Company from Fortune500) AS tempTable);
    – jkavalik
    Commented Jul 11, 2016 at 19:47
  • @jkavalik I tried the code using the 'as temp' statement, but now it says, "Error Code: 1146. Table 'fortune_500.company_financials' doesn't exist". Since I was new to mySQL I built the tables using the schemas tab on the left side. Do I need to build them through query? Thanks for the help! Commented Jul 12, 2016 at 2:06
  • You want a JOIN and not UNION in your case. The tables do not have the same columns so they cannot be unioned. And this error means you are using wrong active database or wrong table name.
    – jkavalik
    Commented Jul 12, 2016 at 4:28
  • 1
    Also, the way you have it, you are puttingRevenue into ID. If ID is AUTO_INCREMENT, leave it out of the INSERT list.
    – Rick James
    Commented Jul 13, 2016 at 18:09

1 Answer 1

1

You need to use JOIN by ID instead of the UNION.
Using JOIN there is no need of the sub-query. Also ( before SELECT is not required after the INSERT INTO.

So the working query is:

INSERT INTO Company (Year, Company, Revenue) 
SELECT FR.Year, FR.Company, CF.Revenue
FROM Fortune500 FR
JOIN Comp_Financials CF ON CF.ID = FR.ID;

Added More explanation in this post, why you need to avoid UNION, and use JOIN for your requirement.

1
  • @AnthonyPotts: If this answer solves your problem, you can accept the answer. So it informs others that your issue is resolved.
    – Arulkumar
    Commented Jul 17, 2016 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.