SQL statement UPDATE

Update is one of the most common SQL statements. The following describes three usage methods of update statements for your reference.

I. 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 = InnoDB AUTO_INCREMENT = 4 default charset = gbk;
 
 
Insert into t_test VALUES (1, 'lavasosft ', '123', NULL );
Insert into t_test VALUES (2, 'Hello', NULL, NULL );
Insert into t_test VALUES (3, 'hahaha', zz, tt );
 
Iii. Test
 
1. set a field
Set the password of the second record bs to 2 in the t_test table '***'.
Update t_test t
Set t. password = '***'
Where t. bs = 2;
 
2. set Multiple Fields
In the t_test table, set the password of the first record bs to 1) to '*' and remark '*'.
Update t_test t
Set t. password = '*', t. remark = '*'
Where t. bs = 1;
 
3. set null Value
In the t_test table, set the password of the third record bs to 3) to null, and the remark to null.
Update t_test t
Set t. password = null, t. remark = null
Where t. bs = 3;
 
This is written according to the standard syntax. In different database systems, there are more writing methods for update, but standard writing is supported. In the preceding three examples, a row is updated every time to illustrate the situation. In practice, you can use the where statement constraints to control the number of updated rows.
 

N + 1 select statement query in SQL

Introduction to select into and insert into select statements in SQL

How to resolve the in range parameter of a select statement in SQL Server

Use the conditional logic of SELECT statements in SQL

Nested SELECT statements in SQL statements