mysql update实现子查询的实例分享

发布时间:2020-11-17编辑:脚本学堂
本文介绍下,在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 子查询效率慢的优化方法详解