[mysql] mysql 在update中实现子查询的方式

最新推荐文章于 2025-02-20 22:50:26 发布

永生天地

最新推荐文章于 2025-02-20 22:50:26 发布

阅读量3.5w

点赞数 3

分类专栏： [Oracle] - [mysql] 文章标签： mysql 2010 warnings null duplicates sqlserver

本文链接：https://blog.youkuaiyun.com/xys_777/article/details/5793565

版权

[Oracle] - [mysql] 专栏收录该内容

4 篇文章

订阅专栏

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)

mysql>

总结：

在写这段代码前，好了sql server的。但是转到mysql里始终不能成功，在不断的看帮助、查资料、测试后发现mysql的update的一些特点

1、update 时，更新的表不能在set和where中用于子查询；

2、update 时，可以对多个表进行更新（sqlserver不行）；

如：update ta a,tb b set a.Bid=b.id ,b.Aid=a.id;

3、update 后面可以做任意的查询，这个作用等同于from；