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