SQL insert multiple rows from query result into a single column of an existing table

Question

I'm new to SQL and I'm struggling with the insert into function. I'm using phpMyAdmin.

I'm trying to take a result of a query, a list (1 column) and add it to a table with more than 1 column with each result being a new row.

For context, I'm working with Olympics data. I have a query that returns a list of countries that had athletes that competed but did not win any medals (country not found in "total medal" table).

-- Find Countries who entered and did not win medals
SELECT find.Country
FROM (
    SELECT DISTINCT Country
    FROM `athletes`
    ) AS find
LEFT JOIN (
    SELECT DISTINCT Country
    FROM `total medals`
    ) AS inside
    ON find.Country = inside.Country
WHERE inside.Country IS NULL;

I am trying to add those 20ish countries to the 'total medals' table. My assumption is that they would be added in with NULLS for every other column.

Country       Gold   Silver   Bronze   Total   Rank by Gold   Rank by Total
[result1]     NULL    NULL     NULL     NULL     NULL           NULL
[result2]     NULL    NULL     NULL     NULL     NULL           NULL
[...etc]      NULL    NULL     NULL     NULL     NULL           NULL

The end goal would have the medal counts be changed to 0s and the Rank by Gold would be 87 and Rank by a Total of 78 for each row. I'm not sure if there would be a way to do that in one step as well, If not, I'm sure I could figure out changing a NULL to something else with.

[result1] & 0 & 0 & 0 & 0 & 87 & 78
[result2] & 0 & 0 & 0 & 0 & 87 & 78
[...etc]  & 0 & 0 & 0 & 0 & 87 & 78

There's a very similar question here that almost got me there, but it's missing the query result.

My understanding of this is that I should be able to plug my previous query into the values section.

INSERT INTO `total medals`(Country)
VALUES ([Query]);

But when I try that, I get a syntax error in line 4 (Select find.Country).

-- Find Countries who entered and did not win medals and add them to the medals table
INSERT INTO `total medals`(Country)
VALUES(
    SELECT find.Country
    FROM (
        SELECT DISTINCT Country
        FROM `athletes`
        ) AS find
    LEFT JOIN (
        SELECT DISTINCT Country
        FROM `total medals`
        ) AS inside
        ON find.Country = inside.Country
    WHERE inside.Country IS NULL
    );

In conclusion:

How do I plug a query into a table with a different width 2 Extra credit - can you also autofill the 0s 87, and 78 in one step?

what does your table look like? which db are you using? – drum Sep 11 '21 at 02:15 — drum, Sep 11 '21 at 02:15
@drum phpMyAdmin implies the OP is using MySQL or MariaDB. – Dai Sep 11 '21 at 02:18 — Dai, Sep 11 '21 at 02:18

Gordon Linoff · Accepted Answer · 2021-09-11T15:27:49.823

0

First, you would phrase the logic using NOT EXISTS . . . at least to simplify it. Then you would add the values for each row that you want. Then you would add the values you want using INSERT . . . SELECT:

INSERT INTO `total medals` (Country, Gold, Silver, Bronze, Total, Rank_by_Gold, Rank_By_Total)
    SELECT DISTINCT a.Country, 0, 0, 0, 87, 78
    FROM athletes a
    WHERE NOT EXISTS (SELECT 1
                      FROM `total medals` tm
                      WHERE tm.Country = a.Country
                     );

edited Sep 11 '21 at 15:27

answered Sep 11 '21 at 12:27

Gordon Linoff

1,242,037
58
646
786

Thanks, @Gordon Linoff Wonderful! It seems that the `VALUES()` part was tripping it up. I got it to work with my original query using your pointers `-- Find Countries who entered and did not win medals then add them to "total medals" INSERT INTO 'total medals' (Country, Gold, Silver, Bronze, Total, 'Rank by Gold', 'Rank by Total') -- replaced underscores with backticks (I should probably avoid spaces in column headers!) SELECT find.Country, 0, 0, 0, 0, 87, 78 -- added the other values after the country calculations` (cont.) – TheTravdrum Sep 11 '21 at 14:18
I had a question about your compact version as well. 1) I see you are aliasing "total medals" as "tm", and "athletes" as "a", but where is the "c" coming from in SELECT DISTINCT **c.Country**? Wouldn't I want the distinct athletes' countries ("a")? 2) I understand the front half, getting a distinct list of athlete's countries that don't show up in the subquery at the end, but the "1" is throwing me off a bit. Is that the index of the first column (Country)? When I tested just the subquery, it gave me a bunch of 1s. Thank you very much for your help! – TheTravdrum Sep 11 '21 at 14:39
@TheTravdrum . . . The `c` was a typo. – Gordon Linoff Sep 11 '21 at 15:27

SQL insert multiple rows from query result into a single column of an existing table

1 Answers1