I have this table:
create table teams (team char(1) primary key, players text);
insert into teams('A', 'Jhon');
insert into teams('B', 'Mark');
Now, how do I add the player 'Carl' in team 'A'?
The column 'players' maybe like a list?
-
do you want to have comma seperated values in players column?tanrich– tanrich2014-06-26 11:55:58 +00:00Commented Jun 26, 2014 at 11:55
-
Try to read en.wikipedia.org/wiki/First_normal_form. It may give you some ideas.Eugene Podskal– Eugene Podskal2014-06-26 11:57:12 +00:00Commented Jun 26, 2014 at 11:57
-
possible duplicate of How to design this Database part?plaes– plaes2014-06-26 12:21:37 +00:00Commented Jun 26, 2014 at 12:21
Add a comment
|
5 Answers
You would do:
insert into teams('A', 'Carl');
after you remove the primary key constraint.
Actually, what you really want is:
create table TeamPlayers (
TeamPlayerId int auto_increment,
team char(1),
players text
);
Then you do the inserts that you want. This is a junction table (sort of). It suggests that you also want a Teams table with one row per team and a Players table with one row per player. Depending on the application, those tables may not be necessary.
Comments
You have made team as primary key so you can't duplicate it.
create some column id make it the primary key then you can add as many members as you want to team A
Comments
This should add Carl:
update teams set players =concat(concat(players,','),'Carl') where team='A'
But it is not a good database design.
Comments
What i suppose you want:
update teams set players = players + ', Carl' where team='A'
better if you keep team and player in separate tables
and use team_id in players table.
Comments
Two way you can do,
one :
By append the new player with old player with cama seprated
update teams set players =concat(concat(players,','),'Carl') where team='A'
Two :
Don't use primary key for team field and add one auto increment fiend with primary key
