Inserting Output of COUNT Statement to Table with MySql.

Question

I've been struggling to come up with a solution to the following problem.

I have two tables with the following structure.

member table:

╔═══════════╦═════════════╦═════════╗
║ member_no ║ member_type ║ team_no ║
╠═══════════╬═════════════╬═════════╣
║   CON123  ║   monthly   ║    12   ║
╠═══════════╬═════════════╬═════════╣
║   CON456  ║   monthly   ║    13   ║
╠═══════════╬═════════════╬═════════╣
║   CON789  ║    annual   ║    13   ║
╚═══════════╩═════════════╩═════════╝

team table:

╔═════════╦════════════╦══════════════╗
║ team_no ║ supervisor ║ member_count ║
╠═════════╬════════════╬══════════════╣
║    12   ║    John    ║       1      ║
╠═════════╬════════════╬══════════════╣
║    13   ║     Joe    ║       2      ║
╠═════════╬════════════╬══════════════╣
║    14   ║    Allan   ║       0      ║
╚═════════╩════════════╩══════════════╝

What I would like to do is something like this when inserting values into the "team" table:

INSERT INTO team (team_no , supervisor , member_count) 
VALUES ("13" , "Joe" , SELECT COUNT(team_no) FROM member WHERE team_no = "13");

Essentially, I would like the member_count column from the "team" table to be populated with the total number of members belonging to that team using the COUNT function on the member table.

I'd be really thankful if anyone can help me with this problem.

Bad idea! Instead whenever you want to know how many members there are in a team, run a query to find it out. This way you dont make a problem for yourself i.e. someone forgetting to add 1 or subtract 1 when the team changes — RiggsFolly
– RiggsFolly, Commented Apr 9, 2017 at 16:53
Possible duplicate of Mysql: Update table with count from same table — Amr Eladawy
– Amr Eladawy, Commented Apr 9, 2017 at 16:54

ScaisEdge · Accepted Answer · 2017-04-09 16:46:19Z

0

you can use an INSERT / SELECT using this sintax

INSERT INTO team (team_no , supervisor , member_count) 
select "13" , "Joe" , COUNT(team_no) 
FROM member 
WHERE team_no = "13"

see mysql doc for more https://dev.mysql.com/doc/refman/5.7/en/insert-select.html

answered Apr 9, 2017 at 16:46

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-04-09 16:53:54Z

0

You can basically do your query with additional parentheses:

INSERT INTO team (team_no , supervisor , member_count) 
    VALUES (13 , 'Joe' , (SELECT COUNT(team_no) FROM member WHERE team_no = 13));

Subqueries require their own parentheses.

Don't use single or double quotes for numbers.

Admittedly, I would probably use insert . . . select, but this is the smallest change to your attempted query.

answered Apr 9, 2017 at 16:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Inserting Output of COUNT Statement to Table with MySql.

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related