A brief understanding of the three uses of standard SQL update statements

1. Environment:

MySQL-5.0.41-win32

Windows XP Professional

2. Establish a 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=MyISAM 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);

3. Testing

1. Set a field

In the table t_test, set the password of the second record (bs=2) to '***'.

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

2. Set multiple fields

In the table t_test, set the password of the first record (bs is 1) to '*' and remark to '*'.

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

3. Set null value

In the table t_test, set the password and remark of the third record (bs is 3) to null and null respectively.

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

Conclusion

This is written according to the standard syntax. In different database systems, there are more ways to write update, but the standard syntax is supported. In order to illustrate the situation, the above three examples update one row each time. In practice, the number of updated rows can be controlled through where statement constraints.