The database updates multiple records with different values, updates multiple fields at the same time, and updates multiple records

The database updates multiple records with different values, updates multiple fields at the same time, and updates multiple records

Requirement

The following two tables are student (student table) and score (test student table)

Statistics: total score and average score of the Gender age = 1 after.

Requirement: use an SQL score table to update the result to the score_sum and score_avg fields of the student table.

Result

Implementation:

If we only need to update one field, MYSQL and ORACLE have the same syntax, just follow a subquery after set, as shown below:

UPDATE student D
Set d. score_sum =
(
SELECT
SUM (B. score)
FROM score B
Where B. studentId = D. id
AND B. examTime> = '2017-03-10'
Group by B. studentId
)
Where d. id =
(
SELECT
E. id FROM
(
SELECT
DISTINCT a. studentId AS id
FROM score
Where a. examTime> = '2017-03-10'
) E
Where e. id = D. id
)
AND d. age = 1;

Now we need to update two fields at the same time. The best way to avoid thinking is to "follow a subquery for each set ",

What if we want to set ten or more fields? Obviously, this method is not suitable for performance.

The method for updating multiple fields in MYSQL and ORACLE is different at the same time. MYSQL needs to connect to the table, and ORACLE can use set (...).

(After reading the following SQL statements, you will find that ORACLE is easy to use and easy to understand)

1) MYSQL implements our final requirement. The statement is as follows:

UPDATE student D
Left join (SELECT
B. studentId,
SUM (B. score) AS s_sum,
ROUND (AVG (B. score), 1) AS s_avg
FROM score B
WHERE B. examTime> = '2017-03-10'
Group by B. studentId) C
ON (C. studentId = D. id)

Set d. score_sum = c. s_sum,
D. score_avg = c. s_avg

Where d. id =
(
SELECT
E. id FROM
(
SELECT
DISTINCT a. studentId AS id
FROM score
Where a. examTime> = '2017-03-10'
) E
Where e. id = D. id
)
AND d. age = 1;

2) ORACLE implements our final requirement. The statement is as follows:

UPDATE student D
SET (D. score_sum, D. score_avg) = (
SELECT
SUM (B. score) AS s_sum,
ROUND (AVG (B. score), 1) AS s_avg
FROM score B
WHERE B. examTime> = '2017-03-10'
And B. studentId = D. id
Group by B. studentId
)
Where d. id =
(
SELECT
E. id FROM
(
SELECT
DISTINCT a. studentId AS id
FROM score
Where a. examTime> = '2017-03-10'
) E
Where e. id = D. id
)
AND d. age = 1;

Two knowledge points used in this article:

1. Update multiple records with different values.

2. Update multiple fields at the same time.

=, Adjust the SQL statement as follows ==============================

UPDATE student D
Left join (SELECT
B. studentId,
SUM (B. score) AS s_sum,
ROUND (AVG (B. score), 1) AS s_avg
FROM score B
WHERE B. examTime> = '2017-03-10'
Group by B. studentId) C
ON (C. studentId = D. id)

Set d. score_sum = IFNULL (c. s_sum, 0 ),
D. score_avg = IFNULL (c. s_avg, 0)

Where d. id =
(
SELECT
E. id FROM
(
SELECT
DISTINCT a. studentId AS id
FROM score
# Where a. examTime> = '2017-03-10'
) E
Where e. id = D. id
)

AND d. age = 1;

The result is as follows:

Test SQL

/* SQLyog Ultimate v10.00 Beta1MySQL-5.5.28: database-test ************************************* *********************************//*! 40101 set names utf8 */;/*! 40101 SET SQL _MODE = ''*/;/*! 40014 SET @ OLD_UNIQUE_CHECKS = @ UNIQUE_CHECKS, UNIQUE_CHECKS = 0 */;/*! 40014 SET @ OLD_FOREIGN_KEY_CHECKS = @ FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0 */;/*! 40101 SET @ OLD_ SQL _MODE = @ SQL _MODE, SQL _MODE = 'no _ AUTO_VALUE_ON_ZERO '*/;/*! 40111 SET @ OLD_ SQL _NOTES = SQL _notes, SQL _NOTES = 0 */; CREATE DATABASE /*! 32312 if not exists */'test '/*! 40100 default character set utf8 */; USE 'test';/* Table structure for table 'score '*/drop table if exists 'score '; create table 'score '('id' int (11) not null AUTO_INCREMENT COMMENT 'id', 'studentid' int (11) default null comment 'student id ', 'subobjectname' varchar (20) default null comment 'subject name', 'score 'float default null comment', 'examtime' datetime default null comment 'test time ', primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 25 default charset = utf8;/* Data for the table 'score '*/insert into 'score' ('id', 'studentid ', 'subobjectname', 'score ', 'examtime') values (2015, 'China', 70, '2017-02-26 18:11:39'), (, 'mat ', 80, '2017-03-26 18:11:50 '), (2015, 'English', 76, '2017-04-26 18:11:56 '), (2015, 'hire', 96, '2017-05-26 18:12:02 '), (2015, 'language \ r \ n mathematics \ r \ n English \ r \ n history \ r \ n ', 84, '2017-02-26 18:11:39 '), (6, 2, 'mat', 56, '2017-03-26 18: 11: 50'), (2015, 'English ', 86, '2017-04-26 18:11:56'), (, 'hire', 45, '2017-05-26 18:12:02 '), (2015, 3, 'China', 87, '2017-02-26 18:11:39'), (2015, 'mat', 98, '2017-03-26 18:11:50 '), (2015, 'English', 67, '2017-04-26 18:11:56 '), (2015, 'hire', 86, '2017-05-26 18:12:02 '), (2015, 'China', 97, '2017-02-26 18:11:39'), (2015, 'mat', 68, '2017-03-26 18:11:50 '), (2015, 'English', 79, '2017-04-26 18:11:56 '), (2015, 'historical', 83, '2017-05-26 18:12:02 '), (2015, 5, 'China', 92, '2017-02-26 18: 11: 39 '), (2015, 'mat', 93, '2017-03-26 18:11:50'), (, 'English ', 65, '2017-04-26 18:11:56 '), (2015, 5, 'historical', 88, '2017-05-26 18:12:02 '), (2015, 'China', 87, '2017-01-05 18:48:48 '), (2015, 'mat', 67, '2017-01-05 18:48:48'), (2015, 6, 'English ', 99, '2017-01-05 18:48:48 '), (24, 6, 'hire', 88, '2017-01-05 18:48:48 '); /* Table structure for table 'student '*/drop table if exists 'student'; create table 'student '('id' int (11) not null AUTO_INCREMENT COMMENT 'id', 'name' varchar (20) default null comment 'name', 'score _ Sum' varchar (20) default null comment 'total score ', 'score _ avg' varchar (20) default null comment 'average title', 'age' int (11) default null comment '1 male 0 female ', primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 7 default charset = utf8;/* Data for the table 'student '*/insert into 'student' ('id ', 'name', 'score _ Sum', 'score _ avg', 'age') values (1, 'xiaoming ', '123 ', '84 ', 1), (2, 'wang', '123', '62. 3', 1), (3, 'lily', '','', 0), (4, 'column ', '123', '76. 7', 1), (5, 'hairy ', '','', 0), (6, 'bright sub', '0', '0', 1 ); /*! 40101 SET SQL _MODE = @ OLD_ SQL _MODE */;/*! 40014 SET FOREIGN_KEY_CHECKS = @ OLD_FOREIGN_KEY_CHECKS */;/*! 40014 SET UNIQUE_CHECKS = @ OLD_UNIQUE_CHECKS */;/*! 40111 SET SQL _NOTES = @ OLD_ SQL _NOTES */;