Example tutorial for using UPDATE statements in MySQL

1. UPDATE
First, set up the test environment:

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (
 bs bigint(20) NOT NULL auto_increment,
 username varchar(20) NOT NULL,
 password varchar(20) default NULL,
 remark varchar(200) default NULL,
 PRIMARY KEY (bs)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk;
 
 
INSERT INTO t_test VALUES (1,'lavasoft','123456',NULL);
INSERT INTO t_test VALUES (2,'hello',NULL,NULL);
INSERT INTO t_test VALUES (3,'haha',zz,tt);

1. set1 fields
In table t_test, set password for the second record (bs for 2) to be '***'.

update t_test t 
  set t.password = '***' 
 where t.bs = 2;

2. set multiple fields
In table t_test, set password for the first record (bs is 1) to be '*' and remark to be '*'.

update t_test t 
  set t.password = '*', t.remark = '*' 
 where t.bs = 1;

3. set null value
In Table t_test, set password for the third record (bs for 3) as null and remark as null.

update t_test t 
  set t.password = null, t.remark = null 
 where t.bs = 3;

This is written according to the standard syntax. There are many more ways to write update in different database systems, but the standard syntax is supported. For the sake of illustration, the above three examples update 1 line at a time. In practice, you can control the number of updated rows through the where statement constraint.

2. Related performance problems in the use of UPDATE and solutions
The function of UPDATE is to update the data in the table. This syntax is similar to INSERT 2. The table name must be supplied along with an SET expression, which can be followed by WHERE to limit the range of updated records.

UPDATE table_anem SET column_name1 = value1, column_name2 = value2,  WHERE ;

The following statement changes age to 24 for records in the users table where id equals 123

UPDATE users SET age = 24 WHERE id = 123;

Again, you can use UPDATE to update the values of multiple fields

UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;

The UPDATE statement above specifies 1 condition through WHERE; otherwise, UPDATE updates the value of all records in the table
For mega data, there should be no problem with mysql.

The problem with your sql is that it is equivalent to modifying the AGE information of all records in THE ONE table, and the modification process is that for each record in ONE, you can go to TWO to query and then modify. Also, there's likely to be a lock or something.
First of all, this KIND of sql should not appear in the business logic, but should be in the background of job.
If 1 wants to do this, try doing it the other way round. If there are not too many different records, find the records in TABLE ONE that have different records from table AGE and modify them, for example, something like the following (maybe the syntax is wrong) :

update ONE . TWO 
set ONE.AGE=TWO.AGE 
where ONE.ID=TWO.ID AND ONE.AGE != TWO.AGE 

When I adjusted the data to 1000W, it could not be updated. Now I will analyze the reasons.
Example: You need to calculate the geographic address of the user log based on its ip address
Table structure:
User log table (2 million records), where address is the field to be populated:

CREATE TABLE `tmp_open_ip` (
 `email` varchar(60) NOT NULL DEFAULT '',
 `address` varchar(50) NOT NULL DEFAULT '',
 `ip` int(10) unsigned NOT NULL DEFAULT '0',
 KEY `email` (`email`),
 KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

ip address database table (440,000 records)

CREATE TABLE `ip` (
`s` int(10) unsigned NOT NULL DEFAULT '0' COMMENT ' start ip',
`e` int(10) unsigned NOT NULL DEFAULT '0' COMMENT ' The end of the ip',
`a` varchar(50) NOT NULL DEFAULT '',
KEY `s` (`s`),
KEY `e` (`e`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

According to the ip field in the user log table tmp_open_ip, the corresponding geographic address should be searched in the ip address database table, and the address should be filled into the address field.
Use the following update statement:

update t_test t 
  set t.password = '***' 
 where t.bs = 2;

0

It ran very slowly on my computer for over an hour (4500s), and I do not know how long it will take.
Unable to see the past, I thought whether it would be faster to use insert, so I redirected a table tmp_open_log and tmp_open_log.
Create a table tmp_open_address, which is the target table of insert. For faster speed, no index is built:

update t_test t 
  set t.password = '***' 
 where t.bs = 2;

1

Execute the insert statement

update t_test t 
  set t.password = '***' 
 where t.bs = 2;

2

Less than 17 s! Originally, I wanted to pour a glass of water and have a rest for a while, but the execution has been completed.

By the time this article is written, the previous update statement has executed 5000s, with no end in sight.
Therefore, when executing update for large data volume, you can consider using insert statement to implement. It may be troublesome, but the benefits brought by high speed far outweigh the troubles!
Postscript:
Kill the update process directly. See how much update does: run

SELECT * FROM `tmp_open_ip` where address!=''

The result is only 11,373. At this rate, it would take N days to run...