Database update multiple record different values, update multiple fields at the same time

Demand

The following two tables student (student table), score (Test score table)

Statistics are required: After 2015-03-10, the scores of the test scores of the sex age=1 were divided into the average.

Requirement: Use a SQL Statistics score table to update the results to the Score_sum and Score_avg fields of the student table.

Results

Realize:

If we only need to update a field, MySQL and Oracle syntax is the same, after the set followed by a subquery, as follows:

UPDATE Student D
SET d.score_sum =
(
SELECT
SUM (B.score)
From Score B
WHERE B.studentid = d.id
and B.examtime >= ' 2015-03-10 '
GROUP by B.studentid
)
WHERE d.id =
(
SELECT
E.id from
(
SELECT
DISTINCT A.studentid as ID
From Score A
WHERE a.examtime >= ' 2015-03-10 '
) E
WHERE e.id = d.id
)
and d.age = 1;

Now we need to update 2 fields at the same time, and the least way to think through the brain is to "follow a subquery for each set".

What if we want to set 10 fields or more fields? Obviously, this is a very inappropriate approach to performance.

Updating multiple fields at the same time the method in MySQL and Oracle is different, MySQL needs to connect the table, Oracle uses set (...) to

(Look at the SQL below and you'll find that Oracle is simple to use and easy to understand)

1) MYSQL implements our final requirements, with the following statements:

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 >= ' 2015-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 A
WHERE a.examtime >= ' 2015-03-10 '
) E
WHERE e.id = d.id
)
and d.age = 1;

2) ORACLE achieves our final requirements, with the following statements:

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 >= ' 2015-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 A
WHERE a.examtime >= ' 2015-03-10 '
) E
WHERE e.id = d.id
)
and d.age = 1;

The 2 points of knowledge used in this article:

1, update multiple records, each record different values.

2. How to update multiple fields at the same time.

======================== will age = 1, no test scores of students give default value 0, adjust SQL 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 >= ' 2015-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 A
# #WHERE a.examtime >= ' 2015-03-10 '
) E
WHERE e.id = d.id
)

and d.age = 1;

The results are as follows:

Test SQL

/*sqlyog Ultimate v10.00 beta1mysql-5.5.28:database-test********************************************************* *!40101 set NAMES UTF8 */;/*!40101 set sql_mode= ' */;/*!40014 set @[email protected] @UNIQUE_CHECKS , unique_checks=0 */;/*!40014 set @[email protected] @FOREIGN_KEY_CHECKS, foreign_key_checks=0 */;/*!40101 set @[ Email protected] @SQL_MODE, sql_mode= ' No_auto_value_on_zero ' */;/*!40111 SET @[email protected] @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 (one) not NULL auto_increment COMMENT ' id ', ' studentid ' int (one) ' DEFAULT NULL COMMENT ' learner ID ', ' subjectname ' varchar () default NULL COMMENT ' account name ', ' score ' float default null COMMENT ' exam score ', ' Examtime ' Datetim E default NULL COMMENT ' exam time ', PRIMARY KEY (' id ')) engine=innodb auto_increment=25 default charset=utf8;/*Data for the table ' score ' */insert into ' score ' (' id ', ' studentid ', ' subjectname ', ' score ', ' examtime ') VALUES (1, 1, ' languages ', 70 , ' 2015-02-26 18:11:39 '), (2,1, ' mathematics ', 80, ' 2015-03-26 18:11:50 '), (3,1, ' English ', 76, ' 2015-04-26 18:11:56 '), (4,1, ' history ', 96, ' 2015-05-26 18:12:02 '), (5,2, ' Chinese \ r \ n \ \ \ \ \ \ \ \ \ \ \ \ \ r \ n history \ r \ n '), 84, ' 2015-02-26 18:11:39 '), (6,2, ' math ', 56, ' 2015-03-26 18:11:50 '), (7,2, ' English ', 86, ' 2015-04-26 18:11:56 '), (8,2, ' history ', 45, ' 2015-05-26 18:12:02 '), (9,3, ' language ', 87, ' 2015-02-26 18:11:39 '), (10,3, ' mathematics ', 98, ' 2015-03-26 18:11:50 '), (11,3, ' English ', 67, ' 2015-04-26 18:11:56 '), (12,3, ' history ', 86, ' 2015-05-26 18:12:02 '), (13,4, ' language ', 97, ' 2015-02-26 18:11:39 '), (14,4, ' math ', 68, ' 2015-03-26 18:11:50 '), (15,4, ' English ', 79, ' 2015-04-26 18:11:56 '), (16,4, ' history ', 83, ' 2015-05-26 18:12:02 '), (17,5, ' language ', 92, ' 2015-02-26 18:11:39 '), (18,5, ' mathematics ', 93, ' 2015-03-26 18:11:50 '), (19,5, ' English ', 65, ' 2015-04-26 18:11:56 '), (20,5, ' history ', 88, ' 2015-05-26 18:12:02 '), (21,6, ' language ', 87, ' 2015-01-05 18:48:48 '), (22,6, ' mathematics ', 67, ' 2015-01-05 18:48:48 '), (23,6, ' English ', 99, ' 2015-01-05 18:48:48 '), (24,6, ' history ', 88, ' 2015-01-0518:48:48 ');/*table structure for table ' student ' */drop table IF EXISTS ' student '; CREATE TABLE ' student ' (' ID ' int (one) not NULL auto_increment COMMENT ' id ', ' name ' varchar ') DEFAULT NULL COMMENT ' name ' , ' score_sum ' varchar (default null COMMENT ' Total ', ' score_avg ' varchar ') default null COMMENT ' average score ', ' age ' int (11 Default NULL COMMENT ' 1 male 0 female ', PRIMARY KEY (' id ')) engine=innodb auto_increment=7 default charset=utf8;/*data for the tab Le ' student ' */insert into ' student ' (' IDs ', ' name ', ' score_sum ', ' score_avg ', ' age ') VALUES (1, ' xiaoming ', ' 252 ', ' 84 ', 1), (2, ' Xiao Wang ') , ' 187 ', ' 62.3 ', 1), (3, ' Lily ', ' ', ' ', ' 0 '), (4, ' pillars ', ' 230 ', ' 76.7 ', 1), (5, ' Mao ', ' ', ' ', ' 0 '), (6, ' bright son ', ' 0 ', ' 0 ', 1);/*!40101 SET [ Email protected]_sql_mode */;/*!40014 Set [email protected]_foreign_key_checks */;/*!40014 set [email  protected]_unique_checks */;/*!40111 SET [email protected]_sql_notes */;

Database update multiple record different values, update multiple fields at the same time