Mysql 使用加入 where 子句更新表

Question

我正在尝试使用 where 子句中的连接来更新我的表。我写了以下代码

UPDATE  order_history SET Paidvalue = Paidvalue + item_id*1.1
WHERE  type='addpack'
and  Addstatus='Active'
and  ( SELECT  count(i.ip)
from  ip_ptc i
inner join  order_history o  ON o.user_id=i.user_id
and  i.date='2018-08-17'
)>=4

它给出了错误You can't specify target table 'order_history' for update in FROM clause我需要根据order_history表和ip_ptc表中的user_id引用来比较计数。 我的错是什么

Answer 1

该错误意味着您同时更新了从中选择的表。您可以通过在子查询中构建临时表来解决此问题

UPDATE order_history 
SET Paidvalue = Paidvalue + item_id*1.1
WHERE type='addpack' and Addstatus='Active'
and 
(
select * from 
(
select count(i.ip) 
from ip_ptc i 
inner join order_history o on o.user_id=i.user_id and i.date='2018-08-17'
) tmp
) >= 4

要正确计数，请尝试此操作

UPDATE order_history h
JOIN 
(
select user_id, count(i.ip) as cnt
from ip_ptc 
where ip_ptc.date = '2018-08-17'
group by user_id 
) t on t.user_id = h.user_id and t.cnt >= 4
SET Paidvalue = Paidvalue + item_id*1.1
WHERE type='addpack' and Addstatus='Active'

Answer 2

它完美地修改了 where 子句

UPDATE order_history SET Paidvalue = Paidvalue + item_id*1.1 
WHERE type='addpack' and Addstatus='Active' 
and ( select * from ( select count(i.ip) from ip_ptc i inner join order_history o on o.user_id=i.user_id and i.date='2018-08-27' AND o.type='addpack' and o.Addstatus='Active' ) tmp)>=4