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

  • 2021-12-05 07:37:48
  • OfStack

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

STEP 3 Test

1. set1 Fields

Set password of record 2 (bs is 2) to '***' in table t_test.


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

2. Multiple fields of set

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


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

3. set null value

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


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, update has more ways to write, but the standard writing is supported. To illustrate the situation, each of the above three examples updates one line. In practice, the number of updated rows can be controlled by where statement constraints.


Related articles: