[MySQL] how MySQL implements subqueries in update

How to Implement subquery in MySQL update

 

--- Test code ---------------

Drop table if exists TB;
Create Table if not exists Tb (name varchar (10), time1 datetime, time2 datetime, time3 varchar (8), time4 varchar (8 ));
Insert Tb (name, time1, time2)
Select '1', '2017-08-04 05:06:26 ', '2017-08-04 05:06:29' Union all
Select '1', '2017-08-04 05:06:33 ', '2017-08-04 09:53:32' Union all
Select '1', '2017-08-04 06:06:26 ', '2017-08-04 07:06:29' Union all
Select '1', '2017-08-05 09:43:10 ', '2017-08-05 12:43:50' Union all
Select '1', '2017-08-05 05:43:56 ', '2017-08-05 07:23:33' Union all
Select '1', '2017-08-06 09:43:56 ', '2017-08-06 14:55:59' Union all
Select '1', '2017-08-07 09:04:56 ', '2017-08-07 17:43:56' Union all
Select '1', '2017-08-08 08:56:10 ', null;

Update TB set time3 = timediff (time2, time1 );
Select * from TB;

Update tb,
(Select sec_to_time (sum (time_to_sec (time3) COL, max (time1) time, name
From TB group by date_format (time1, '% Y-% m-% D') B
Set time4 = B. Col
Where a. Name = B. Name and A. time1 = B. time;
Select * from TB;

 

 

 

--- The following is the running process ------------------------------

Mysql> Create Table if not exists Tb (name varchar (10), time1 datetime, time2 date
Time, time3 varchar (8), time4 varchar (8 ));
Query OK, 0 rows affected (0.03 Sec)

Mysql> insert Tb (name, time1, time2)
-> Select '1', '2017-08-04 05:06:26 ', '2017-08-04 05:06:29' Union all
-> Select '1', '2017-08-04 05:06:33 ', '2017-08-04 09:53:32' Union all
-> Select '1', '2017-08-04 06:06:26 ', '2017-08-04 07:06:29' Union all
-> Select '1', '2017-08-05 09:43:10 ', '2017-08-05 12:43:50' Union all
-> Select '1', '2017-08-05 05:43:56 ', '2017-08-05 07:23:33' Union all
-> Select '1', '2017-08-06 09:43:56 ', '2017-08-06 14:55:59' Union all
-> Select '1', '2017-08-07 09:04:56 ', '2017-08-07 17:43:56' Union all
-> Select '1', '2017-08-08 08:56:10 ', null;
Query OK, 8 rows affected (0.00 Sec)
Records: 8 duplicates: 0 Warnings: 0

Mysql>
Mysql> Update TB set time3 = timediff (time2, time1 );
Query OK, 7 rows affected (0.00 Sec)
Rows matched: 8 changed: 7 Warnings: 0

Mysql> select * from TB;
+ ------ + --------------------- + ------------------- + ---------- + ------- +
| Name | time1 | time2 | time3 | time4 |
+ ------ + --------------------- + ------------------- + ---------- + ------- +
| 1 | 05:06:26 | 05:06:29 | 00:00:03 | null |
| 1 | 05:06:33 | 09:53:32 | 04:46:59 | null |
| 1 | 06:06:26 | 07:06:29 | 01:00:03 | null |
| 1 | 09:43:10 | 12:43:50 | 03:00:40 | null |
| 1 | 05:43:56 | 07:23:33 | 01:39:37 | null |
| 1 | 09:43:56 | 14:55:59 | 05:12:03 | null |
| 1 | 09:04:56 | 17:43:56 | 08:39:00 | null |
| 1 | 08:56:10 | null |
+ ------ + --------------------- + ------------------- + ---------- + ------- +
8 rows in SET (0.00 Sec)

Mysql>
Mysql> Update tb,
-> (Select sec_to_time (sum (time_to_sec (time3) COL, max (time1) time, name
-> From TB group by date_format (time1, '% Y-% m-% D') B
-> Set time4 = B. Col
-> Where a. Name = B. Name and A. time1 = B. time;
Query OK, 4 rows affected (0.00 Sec)
Rows matched: 5 changed: 4 Warnings: 0

Mysql> select * from TB;
+ ------ + --------------------- + ---------- +
| Name | time1 | time2 | time3 | time4 |
+ ------ + --------------------- + ---------- +
| 1 | 05:06:26 | 05:06:29 | 00:00:03 | null |
| 1 | 05:06:33 | 09:53:32 | 04:46:59 | null |
| 1 | 06:06:26 | 07:06:29 | 01:00:03 | 05:47:05 |
| 1 | 09:43:10 | 12:43:50 | 03:00:40 | 04:40:17 |
| 1 | 05:43:56 | 07:23:33 | 01:39:37 | null |
| 1 | 09:43:56 | 14:55:59 | 05:12:03 | 05:12:03 |
| 1 | 09:04:56 | 17:43:56 | 08:39:00 | 08:39:00 |
| 1 | 08:56:10 | null |
+ ------ + --------------------- + ---------- +
8 rows in SET (0.00 Sec)

Mysql>

 

Summary:

Before writing this code, you have finished SQL Server. However, it cannot be successfully transferred to MySQL. After constantly reading help, checking information, and testing, we find some features of MySQL update.

 

1. During update, the updated table cannot be used in set and where for subqueries;

2. During update, you can update multiple tables (not SQL Server );

For example, update ta a, tb B set a. Bid = B. ID, B. Aid = A. ID;

3. Any query can be performed after update, which is equivalent to from;