SQLite: How to retrieve data from column in one table using SELECT to insert retrieved data in another table

I am attempting to use SQLite to retrieve data from the main table (mutants.info) and insert data from that table into a secondary table (mutants.teams). The main table has multiple columns, but I am only interested in retrieving data from one, referred to as ‘team’. My goal is to have the script retrieve data from the ‘team’ field/column and insert that data into the secondary table and then count the number of times a certain team name appears in the field.

I wrote the following SQLite script, but it’s not populating the secondary table the way I want it to.

INSERT OR REPLACE INTO "mutants.teams" (team,members) Values(
(SELECT team FROM "mutants.info"),
(SELECT COUNT(*) FROM "mutants.info" WHERE team = (SELECT team FROM "mutants.info"))
);

When I try to run this script, it populates the secondary table with the first ‘team name’ it sees in the ‘team’ column in the main table, but does not populate any other ‘team names’ that are present in the ‘team’ field. How can I make this script pull in the other ‘team names’ that appear in the ‘team’ column?

Table Configs are as follows:

+-----------------------------------------------------------+
|                           main                            | 
+-----------------------------------------------------------+
|id |mutant  |powers             |team         |location    |   
+-----------------------------------------------------------+
|1  |Veto    |Psionic            |Ninjas       |China       |    
+-----------------------------------------------------------+
|2  |Wrecker |Enhanced Strength  |The Crew     |Chicago     |   
+-----------------------------------------------------------+
|3  |Atlas   |Godlike Powers     |The Gods     |Heaven      |   
+-----------------------------------------------------------+
|4  |Aria    |Sonic Energy Powers|The Crew     |Chicago     |
+-----------------------------------------------------------+

+-----------------------+
|       secondary       |
+-----------+-----------+
| team      | members   |
+-----------+-----------+
| The Crew  | 13        |
+-----------+-----------+
| Ninjas    | 27        |
+-----------+-----------+
| The Gods  | 127       |
+-----------+-----------+
| Chosen    | 600       |
+-----------+-----------+

The goal of the script is to retrieve the data from the team field in the main table and populate the second table with that information and then count the number of times the team name appears in the main table.

I’m having difficulty getting the right coding to retrieve this data from the first table and insert it into the second table. I’ll appreciate any help that someone can offer in assisting me with this matter.

Answer

I suspect that what you want is this:

INSERT INTO "mutants.teams" (team, members)
SELECT team, COUNT(*) 
FROM "mutants.info"
GROUP BY team

This query selects all the (distinct) team names from "mutants.info" and inserts them in "mutants.teams" with the number of times each team appears in "mutants.info".

I don’t know why you use in your code INSERT OR REPLACE instead of just INSERT.
If there are already rows in "mutants.teams" and you want them replaced by the new rows if there is a unique constraint violation on the team’s name then fine.

Advertisement

Answer