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.