A simple understanding of the three uses of update statements in standard SQL

I. Environment:

MySQL-5.0.41-win32

Windows XP professional

Second, 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);

III. Testing

1. Set a field

Set the password of the second record (bs = 2) to '***' in table t_test.

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

2. Set multiple fields

Set password to '*' and remark to '*' for the first record (bs = 1) in table t_test.

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

3, set null value

Set password to null and remark to null for the third record (bs = 3) in table t_test.

update t_test tset t.password = null, t.remark = nullwhere t.bs = 3;

conclusion

This is written according to the standard syntax, in different database systems, update has more writing, but the standard writing is supported. The above three examples are updated one line at a time to illustrate the situation. In practice, the number of update rows can be controlled by the where statement constraint.