How do I add multiple values to a column in SQL which already has other rows filled-CodePudding

I'm trying to update this table (name: sports_club):

 ---- ----------- ---------------- 
| id | name      | sport_interest |
 ---- ----------- ---------------- 
|  7 | Winston   | NULL           |
|  8 | Winnefer  | NULL           |
|  9 | Winsteen  | NULL           |
| 10 | Wincifer  | NULL           |
| 11 | Winster   | NULL           |
| 12 | Winstonia | NULL           |
| 13 | Wilson    | NULL           |
| 14 | Winnerva  | NULL           |
| 15 | WinDiesel | NULL           |
 ---- ----------- ----------------

I'm trying to add values to just the sport_interest column of the existing records, I tried to do that by using:

insert into sport_interest values ('cricket'), ('football'), etc.

But it did not work as it was trying to add it to new records which I don't want to do. I'm using MySQL

CodePudding user response：

You can do the updates manually like:

update sport_interest set sport_interest = 'cricket' where id=7;
update sport_interest set sport_interest = 'football' where id=8;

And so on...

But, if you have the id of the values that you need to update it is much easier.

You can create another table like:

create table sport_interest_bak (
id int ,
sport_interest varchar(50));

You can use LOAD DATA to insert the data on the new table and then update using inner join, it is simpler and much faster.

update sport_interest s
inner join sport_interest_bak sb on s.id=sb.id
set s.sport_interest=sb.sport_interest;

I suggest reading https://www.mysqltutorial.org/mysql-basics/ or https://www.techonthenet.com/mysql/index.php this are much better than W3Schools

CodePudding user response：

Use the 'Update' command for existing entries. "UPDATE table SET sport_interest = 'football'" should work.

Use WHERE if you do not want all cells to update. ex. UPDATE table SET sport_interest = 'football' WHERE name = 'Winston'