CODEWITHSUNDEEP
mysqlsql
user3608898
user3608898

Reputation: 39

Add (not update) fields into column sql using insert

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?

Upvotes: 0

Views: 35

Answers (5)

R D
R D

Reputation: 1332

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

Upvotes: 0

donstack
donstack

Reputation: 2715

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.

Upvotes: 0

Jens
Jens

Reputation: 69505

This should add Carl:

update teams set players =concat(concat(players,','),'Carl') where team='A'

But it is not a good database design.

Upvotes: 1

SparkOn
SparkOn

Reputation: 8956

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271191

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.

Upvotes: 2

Related Questions