Skip to content
Advertisement

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

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

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement