mysql update实现子查询的实例分享
本节内容：
mysql update子查询。
例句如下：
---测试代码---------------
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','2010-08-04 05:06:26 ','2010-08-04 05:06:29 ' union all
select '1','2010-08-04 05:06:33 ','2010-08-04 09:53:32 ' union all
select '1','2010-08-04 06:06:26 ','2010-08-04 07:06:29 ' union all
select '1','2010-08-05 09:43:10 ','2010-08-05 12:43:50 ' union all
select '1','2010-08-05 05:43:56 ','2010-08-05 07:23:33 ' union all
select '1','2010-08-06 09:43:56 ','2010-08-06 14:55:59 ' union all
select '1','2010-08-07 09:04:56 ','2010-08-07 17:43:56 ' union all
select '1','2010-08-08 08:56:10 ',null;
update tb set time3=timediff(time2,time1);
select * from tb;
update tb a,
(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;
---运行过程如下------------------------------
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','2010-08-04 05:06:26 ','2010-08-04 05:06:29 ' union all
-> select '1','2010-08-04 05:06:33 ','2010-08-04 09:53:32 ' union all
-> select '1','2010-08-04 06:06:26 ','2010-08-04 07:06:29 ' union all
-> select '1','2010-08-05 09:43:10 ','2010-08-05 12:43:50 ' union all
-> select '1','2010-08-05 05:43:56 ','2010-08-05 07:23:33 ' union all
-> select '1','2010-08-06 09:43:56 ','2010-08-06 14:55:59 ' union all
-> select '1','2010-08-07 09:04:56 ','2010-08-07 17:43:56 ' union all
-> select '1','2010-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 | 2010-08-04 05:06:26 | 2010-08-04 05:06:29 | 00:00:03 | NULL |
| 1 | 2010-08-04 05:06:33 | 2010-08-04 09:53:32 | 04:46:59 | NULL |
| 1 | 2010-08-04 06:06:26 | 2010-08-04 07:06:29 | 01:00:03 | NULL |
| 1 | 2010-08-05 09:43:10 | 2010-08-05 12:43:50 | 03:00:40 | NULL |
| 1 | 2010-08-05 05:43:56 | 2010-08-05 07:23:33 | 01:39:37 | NULL |
| 1 | 2010-08-06 09:43:56 | 2010-08-06 14:55:59 | 05:12:03 | NULL |
| 1 | 2010-08-07 09:04:56 | 2010-08-07 17:43:56 | 08:39:00 | NULL |
| 1 | 2010-08-08 08:56:10 | NULL | NULL | NULL |
+------+---------------------+---------------------+----------+-------+
8 rows in set (0.00 sec)
mysql>
mysql> update tb a,
-> (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 | 2010-08-04 05:06:26 | 2010-08-04 05:06:29 | 00:00:03 | NULL |
| 1 | 2010-08-04 05:06:33 | 2010-08-04 09:53:32 | 04:46:59 | NULL |
| 1 | 2010-08-04 06:06:26 | 2010-08-04 07:06:29 | 01:00:03 | 05:47:05 |
| 1 | 2010-08-05 09:43:10 | 2010-08-05 12:43:50 | 03:00:40 | 04:40:17 |
| 1 | 2010-08-05 05:43:56 | 2010-08-05 07:23:33 | 01:39:37 | NULL |
| 1 | 2010-08-06 09:43:56 | 2010-08-06 14:55:59 | 05:12:03 | 05:12:03 |
| 1 | 2010-08-07 09:04:56 | 2010-08-07 17:43:56 | 08:39:00 | 08:39:00 |
| 1 | 2010-08-08 08:56:10 | NULL | NULL | NULL |
+------+---------------------+---------------------+----------+----------+
8 rows in set (0.00 sec)
总结：
1、update 时，更新的表不能在set和where中用于子查询；
2、update 时，可以对多个表进行更新（sqlserver不行）；
如：update ta a,tb b set a.Bid=b.id ,b.Aid=a.id;
3、update 后面可以做任意的查询，这个作用等同于from；
您可能感兴趣的文章：
mysql子查询的实例总结
mysql多表子查询与左连接的代码
mysql子查询使用EXISTS命令的例子
mysql子查询中EXISTS用法的例子
mysql子查询的五种方式
mysql 子查询与join性能对比
mysql子查询的用法举例
mysql in 子查询效率慢的优化方法详解
您可能感兴趣的文章：
mysql 子查询与join性能对比
mysql update更新关联表的例子
mysql子查询的五种方式
mysql多表子查询与左连接的代码
mysql递归查询树形叶子
mysql联表Update更新的例子
mysql递归查询替代函数实例
分享:Mysql更新字段中部分数据的方法
mysql子查询的用法举例
mysql 随机生成某个范围内的整数