文档章节

mysql 执行复杂 update 更新, 关联表 以及 使用sum()等函数

之渊
 之渊
发布于 2018/07/03 10:54
字数 1937
阅读 1324
收藏 2

使用 mysql 就不太强,而且写的复杂SQL多是 select 查询语句,最怕复杂的 update 语句了。

所以在这里总结一下

  1.  关联需要更新的表或者根据 需要更新表,来执行更新

 有一张订单明细表,我需要 更新 明细表里面的 金额和重量, 这个 更新的依据是 根据 订单明细表里面的  某些数据的 来进行 算出来 重量或者金额 来进行更新的。

当然 这个订单明细表 需要根据 订单主表 来进行查询出来。

 

首先 第一步 我写出来的SQL ,看起来也不算复杂

UPDATE gwqmshop_process_order_item set total_weight=1*count * (
	 SELECT units.unitWeight from (
	SELECT isrc.total_weight/isrc.count as unitWeight from	gwqmshop_process_order_item as isrc 
   where src_order_item_id=isrc.id 	
	) as units
	),
 total_amount=total_weight*price
 where id in (
	SELECT item.id from 
  (
	SELECT it.id  from  gwqmshop_process_order_item as it
 where 
it.disabled=0 
	and it.order_id in (
	SELECT o.id from gwqmshop_process_order o  where o.disabled=0 and o.data_type=3 and o.createtime>'2018-05-26 22:00:00'
	) 
  ) item
);

 

更新的时候 where 语句必须 这样的写法, 

否则 mysql 就会报错,因为 mysql 是不能 通过 查询 自身然后来进行 更新的。

报错内容:

MYSQL之You can't specify target table for update in FROM clause

 

参考地址: https://blog.csdn.net/z_youarethebest/article/details/53785487

 

上面的刚才我写的SQL语句, 是没有报错, 可是 执行更新之后, 更新的结果都 是 被更新为 null 了。

我这里是 mysql 5.6 不知道是不是 mysql的bug , 即没有报错,又被更新为 null 了。。。

原因是 因为  where src_order_item_id=isrc.id      这个语句, 在 mysql 中 是 获取 不到  

订单明细表 里面的 src_order_item_id  这个字段内容的。

如果我们改为 :

where isrc.id=gwqmshop_process_order_item.src_order_item_id

mysql 就会报错了, 报错是因为 找不到  gwqmshop_process_order_item 表。

其实不管是 update 语句,还是 select 语句,  字段 通过  查询其他表,但是又需要更加当前查询的表的内容取管理的话,都是会报错的。

因为 最外层的表的内容传不去里面的 临时表的。

例子 :


 SELECT  i.id ,i.src_order_item_id,i.total_amount,i.total_weight,i.count * ( 
	SELECT isrc.total_weight/isrc.count
	from gwqmshop_process_order_item isrc where isrc.id =i.src_order_item_id
) 
 from gwqmshop_process_order_item i 
where i.disabled=0 
	and i.order_id in (
	SELECT o.id from gwqmshop_process_order o  where o.disabled=0 and o.data_type=3 and o.createtime>'2018-05-26 22:00:00'
	) ;


上面的语句肯定是没有问题的,如果改动一下就有问题了

 SELECT  i.id ,i.src_order_item_id,i.total_amount,i.total_weight,i.count * (
  SELECT units.unitWeight from  
	(SELECT isrc.total_weight/isrc.count  as unitWeight
	from gwqmshop_process_order_item isrc where isrc.id =i.src_order_item_id
	)  as units
) 
 from gwqmshop_process_order_item i 
where i.disabled=0 
	and i.order_id in (
	SELECT o.id from gwqmshop_process_order o  where o.disabled=0 and o.data_type=3 and o.createtime>'2018-05-26 22:00:00'
	) ;


然后就会报错 :
Unknown column 'i.src_order_item_id' in 'where clause'



那为什么更新的时候不能使用第一种的方式呢?

	UPDATE gwqmshop_process_order_item set gwqmshop_process_order_item.total_weight=1*gwqmshop_process_order_item.count * (
	
	SELECT isrc.total_weight/isrc.count as unitWeight from	gwqmshop_process_order_item as isrc 
   where isrc.id=gwqmshop_process_order_item.src_order_item_id 	
	
	),
 gwqmshop_process_order_item.total_amount=gwqmshop_process_order_item.total_weight*gwqmshop_process_order_item.price
 where gwqmshop_process_order_item.id in (
	SELECT item.id from 
  (
	SELECT it.id  from  gwqmshop_process_order_item as it
 where 
it.disabled=0 
	and it.order_id in (
	SELECT o.id from gwqmshop_process_order o  where o.disabled=0 and o.data_type=3 and o.createtime>'2018-05-26 22:00:00'
	) 
  ) item
);

上面的更新语句就会爆错,还是之前的错误,不能通过查询 自身来更新 
报错:
 You can't specify target table 'gwqmshop_process_order_item' for update in FROM clause








 

 

上面的办法都不能成功,然后我也乱写了一通,都搞不定,,,都自己的错,写代码写出BUG了,必须执行更新语句,,,自己的坑,自己填。。。

网上乱看,然后 自己乱写,乱调试:

最终解决update SQL语句:


UPDATE gwqmshop_process_order_item,gwqmshop_process_order_item units

 set gwqmshop_process_order_item.total_weight=1*gwqmshop_process_order_item.count * (units.total_weight/units.count),
 gwqmshop_process_order_item.total_amount=gwqmshop_process_order_item.total_weight*gwqmshop_process_order_item.price
 where gwqmshop_process_order_item.id in (
	SELECT item.id from 
  (
	SELECT it.id  from  gwqmshop_process_order_item as it
 where 
it.disabled=0 
	and it.order_id in (
	SELECT o.id from gwqmshop_process_order o  where o.disabled=0 and o.data_type=3 and o.createtime>'2018-05-26 22:00:00'
	) 
  ) item
) and units.id = gwqmshop_process_order_item.src_order_item_id;


 

原来只需要  update 的是, 再 来一个自身表 即可。

因为之前 没有这样想是因为 感觉可能是 会 报错,因为查询了自身表又更新了。。。

- You can't specify target table 'gwqmshop_process_order_item' for update in FROM clause

因为没有报错,可以成功是因为 mysql 的 底层区分开了吧。

 gwqmshop_process_order_item,gwqmshop_process_order_item units 可能就是两个 不会产生关联的表吧。

 

 

2.   update 执行,使用SUM() 函数

 

订单明细表,已经更新了 重量和金额了,那么 还需要 更新 订单主表啊,因为订单主表也有 总的 重量和金额。。。

看起来挺简单,更加明细表求和,更加即可。

估计大家一般会这样写:

## 再更新订单的 总理论重量和总理论价格

update gwqmshop_process_order 
set total_weight =(
sum(
SELECT it1.total_weight  from  gwqmshop_process_order_item as it1
where it1.order_id=id and it1.disabled=0
)
),
set total_price=(
sum(
SELECT it2.total_amount  from  gwqmshop_process_order_item as it2
where it2.order_id=id and it2.disabled=0
)
)
where id in 
(
 SELECT mo.id from (
SELECT o.id as id from gwqmshop_process_order o  where o.disabled=0 and o.data_type=3 and o.createtime>'2018-05-26 22:00:00'
	) as mo
);

 

但是 执行之后 报错:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT it1.total_weight  from  gwqmshop_process_order_item as it1
where it1.ord' at line 4

语法错误,什么鬼,,,没有错误啊。反正就是不行。

 

看不问题,我试着简化一下SQL:

update gwqmshop_process_order ,
gwqmshop_process_order_item  it1 
set gwqmshop_process_order.total_weight =sum(it1.total_weight)
where gwqmshop_process_order.id in 
(
 SELECT mo.id from (
SELECT o.id as id from gwqmshop_process_order o  where o.disabled=0 and o.data_type=3 and o.createtime>'2018-05-26 22:00:00'
	) as mo
)
and it1.order_id=gwqmshop_process_order.id and it1.disabled=0
;

 

就爆出来 :

 Invalid use of group function

看起来 感觉是 使用不了 sum 函数啊。。。

想想也是,  sum 函数也算分组函数吧,分组求和的。。。

我们一般使用 sum 函数都是 在 select 语句中的,  没有这样直接使用的。。。

所以要改一下, 随便试了一下,没成功, 去百度看看

可以参考: https://stackoverflow.com/questions/653826/update-with-sum-in-mysql

UPDATE Table_NAme SET PAR= summedValue
FROM   TAble_NAME t
JOIN (
  SELECT ID, SUM(S_val) as summedvalue 
  FROM TABLE_NAME GROUP BY ID
  ) s on t.ID = s.ID

 

这样子 感觉 好像行的通, 可是怎么使用MYSQL 写出来?

 

试着用join 关联:

update gwqmshop_process_order ,
 inner join 
(
SELECT gwqmshop_process_order_item.order_id as order_id ,sum(gwqmshop_process_order_item.total_weight) as sumweight  from gwqmshop_process_order_item
where gwqmshop_process_order_item.disabled=0 
) as itsum on itsum.order_id=gwqmshop_process_order.id

set gwqmshop_process_order.total_weight =itsum.sumweight 
where gwqmshop_process_order.id in 
(
 SELECT mo.id from (
SELECT o.id as id from gwqmshop_process_order o  where o.disabled=0 and o.data_type=3 and o.createtime>'2018-05-26 22:00:00'
	) as mo
)
;


报错。。。
check the manual that corresponds to your MySQL server version for the right syntax to use near 'inner join 
(
SELECT gwqmshop_process_order_item.order_id as order_id ,sum(gwq' at line 2




试试下面的:

update gwqmshop_process_order ,
(
SELECT gwqmshop_process_order_item.order_id as order_id ,sum(gwqmshop_process_order_item.total_weight) as sumweight  from gwqmshop_process_order_item
 GROUP BY  gwqmshop_process_order_item.order_id
) as itsum
left  JOIN itsum itsum1 on itsum1.order_id=gwqmshop_process_order.id
set gwqmshop_process_order.total_weight =itsum1.sumweight
where gwqmshop_process_order.id in 
(
 SELECT mo.id from (
SELECT o.id as id from gwqmshop_process_order o  where o.disabled=0 and o.data_type=3 and o.createtime>'2018-05-26 22:00:00'
	) as mo
)

;




 Table 'yxshop.itsum' doesn't exist  报错,还是不行。
















 

 

最终解决办法:


于是使用下面的:


update gwqmshop_process_order ,
 (
SELECT gwqmshop_process_order_item.order_id as order_id ,sum(gwqmshop_process_order_item.total_weight) as sumweight,
 sum(gwqmshop_process_order_item.total_amount) as sumamount
  from gwqmshop_process_order_item
 GROUP BY  gwqmshop_process_order_item.order_id
) as itsum
set gwqmshop_process_order.total_weight =itsum.sumweight,
gwqmshop_process_order.total_price=itsum.sumamount

where gwqmshop_process_order.id in 
(
 SELECT mo.id from (
SELECT o.id as id from gwqmshop_process_order o  where o.disabled=0 and o.data_type=3 and o.createtime>'2018-05-26 22:00:00'
	) as mo
)
and  itsum.order_id=gwqmshop_process_order.id
;

 

上面的SQL在 mysql 5.6 下面没有问题,

 

但是在 mysql5.7 下面就报错:

You can't specify target table 'gwqmshop_process_order' for update in FROM clause
 

也是奇怪,还好生产环境使用 mysql5.6  

 

mysql 5.7 的报错,不知道是不是BUG,不然为什么mysql5.6 没事?

而且从报错的角度来看,,,不知道怎么会出现这样错误??

 

我也不还知道怎么办了

 

因为 


update gwqmshop_process_order
set total_price=total_price

where 

id in 
(
 SELECT mo.id from (
SELECT o.id as id from gwqmshop_process_order o  where o.disabled=0 and o.data_type=3 and o.createtime>'2018-05-26 22:00:00'
    ) as mo
)

;
这样子在 mysql 5.7 下面行不通, 

有最笨的方式就是 拼出来 执行的所有的 update SQL, 

先把 所有的订单ID查询出来,然后根据订单id 去更新了,

也就是 不采用批量更新了,采用 单表更新了。

 

 

 

 

 

 

 

 

© 著作权归作者所有

之渊
粉丝 14
博文 649
码字总数 200420
作品 0
佛山
程序员
私信 提问
新手MySQL工程师必备命令速查手册

作者介绍 MySQL的基本操作可以包括两个方面:MySQL常用语句如高频率使用的增删改查(CRUD)语句和MySQL高级功能,如存储过程、触发器、事务处理等。而这两个方面又可以细分如下: 1、MySQL常...

DBAplus社群
2018/05/17
0
0
《MySQL必知必会》笔记1

2016/2/21 数据库:保存有组织的数据的容器(通常是一个文件或一组文件) 数据库管理系统(DBMS):用于访问数据库。 表:某种特定类型数据的结构化清单。相同的数据库中表的名字是唯一的 模...

fxdhdu
2016/03/01
19
0
《MySQL必知必会》读书笔记(四) 22~30章 完结篇

1、视图 视图不包含表中应该有的任何列或数据,它包含的是一个SQL查询。在视图创建之后,可以用于表基本相同的方式利用它们。可以对视图执行SELECT操作,过滤和排序数据,将视图联结到其他视...

一万
2016/02/08
339
0
好程序员Java教程:SQL语言之视图

好程序员Java教程:SQL语言之视图,前言: 本章我们将学习MySQL中的视图,视图是数据库中的重要组件,本文将从视图是什么,视图的作用,创建和使用视图以及修改视图这些方面讲解视图。 视图是...

好程序员IT
06/21
46
0
mysql bin_log 日志格式详解

MySQL 5.5 中对于二进制日志 (binlog) 有 3 种不同的格式可选:Mixed,Statement,Row,默认格式是 Statement。总结一下这三种格式日志的优缺点。 MySQL Replication 复制可以是基于一条语句 ...

kevin89
2012/09/10
649
0

没有更多内容

加载失败,请刷新页面

加载更多

如何使用soapUI模拟webservice客户端发送请求

参考资料 https://jingyan.baidu.com/article/cbcede0712849a02f40b4d88.html 左边是请求参数,可以自己填写!按着那个绿色三角箭头可以模拟发送请求,右边是返回的报文 soapui如何发送xml格...

故久呵呵
40分钟前
5
0
Java Security 介绍

1.介绍 Java平台设计的重点是安全性。在其核心,java语言本身是类型安全的并且提供了垃圾自动回收,这使其增加了应用程序代码的健壮性。安全的类加载以及验证机制确保了只有合法的代码才能够...

lixiaobao
46分钟前
5
0
Niushop开源商城系统-分销商管理

分销商管理 1.分销员的招募与管理 如何申请成为分销员? 在wap端个人中心满足之前设置的升级条件,可以申请分销员 开启分销商审核,需要在后台分销商管理——》待审核处进行审核通过。 通过完...

niushop-芳
47分钟前
5
0
为什么大公司一定要使用 DevOps?

究竟什么是DevOps? 要想回答这个问题,首先要明确DevOps这个过程参与的人员是谁,即开发团队和IT运维团队。那么,DevOps的意图是什么呢?即在两个团队之间,建立良好的沟通和协作,更快更可靠...

cs平台
49分钟前
5
0
高危预警|RDP漏洞或引发大规模蠕虫爆发,用户可用阿里云免费检测服务自检,建议尽快修复

2019年9月6日,阿里云应急响应中心监测到Metasploit-framework官方在GitHub空间公开了针对Windows远程桌面服务远程命令执行漏洞(CVE-2019-0708)的利用代码。利用该代码,无需用户交互操作,即...

Mr_zebra
54分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部