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

最新推荐文章于 2026-06-09 09:03:45 发布

转载最新推荐文章于 2026-06-09 09:03:45 发布 · 5.4k 阅读

本内容遵循CC 4.0 BY-SA版权协议

原文链接：https://my.oschina.net/ouminzy/blog/1839214

本文详细介绍了在MySQL中遇到的无法在FROM子句中指定目标表进行更新的问题，以及在UPDATE语句中使用SUM()函数时遇到的错误。作者通过实例分享了解决这些问题的步骤和最终解决方案，包括如何处理关联表更新和在UPDATE语句中正确使用SUM()。

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

所以在这里总结一下

关联需要更新的表或者根据需要更新表，来执行更新

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

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

首先第一步我写出来的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 去更新了，

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

转载于:https://my.oschina.net/ouminzy/blog/1839214

标签