Detailed explanation of the use of update statement in MySQL database

韦小宝
Release: 2018-01-30 10:34:22
Original
6120 people have browsed it

In this article, we will talk about how toupdatetheMySQL 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
Copy after login

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);
Copy after login

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 (中文意思大概是:不能对同一张表进行同时读写)
Copy after login

4. Share a little trick: UseVariablesAdd 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 );
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!