Detailed explanation of the use of update statement in MySQL database

In this article, we will talk about how to update the MySQL database. Adding, deleting, modifying and checking the database is one of the basics we must know. Knowing this addition We can write more things based on deletion, modification and checking. Without further ado, let’s take a look at the content of this article!

1. The first one: Use table b data to update table a.

update Player as a ,PlayerSet as b
set a.role_id=b.set_value 
where a.role_id=b.set_key

2. The second one: Also use table b data to update table a. It’s just that the method is different

update RoleSet
set_key=(SELECT name FROM Player where id = RoleSet.set_value);

3. The third method: use an intermediate table to solve the following error

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxxxxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
（中文意思大概是：不能对同一张表进行同时读写）

4. Share a little trick: Use VariablesAdd non-repeating suffix

set @i:=1;
update Group 
SET name=CONCAT(name,'_', (@i:=@i+1))
where name in
(
SELECT a.GroupName
from
(
        SELECT name  as GroupName
        FROM Group 
        GROUP BY name 
        HAVING count(*) > 1
) as a
);

The content above is all the content in the MySQL database update. If you are not satisfied with it, just read moreExerciseA few times will definitely help us in our future development career! !

A few more articles:

Detailed explanation of update and insert commonly used in mysql

update update, Batch batch update insert data into the table, insert data in the table into another table insert ignore ignore duplicate data insertion error issue update update.

How to use MySQL database select for update

Mysql SELECT FOR UPDATE MySQL uses SELECT ... FOR UPDATE to confirm before transaction writing

The above is the detailed content of Detailed explanation of the use of update statement in MySQL database. For more information, please follow other related articles on the PHP Chinese website!