in-where子句更新表

f1tvaqid 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(226)

我正在尝试使用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 我需要根据数字来比较计数 user_id 两种情况下的引用 order_history 以及 ip_ptc table。我怎么了

mysql sql-update

来源：https://stackoverflow.com/questions/52035683/mysql-update-table-using-join-in-where-clause

2条答案

按热度按时间

vaqhlq811#

该错误意味着您同时更新正在从中选择的表。您可以通过在子查询中构建临时表来克服这个问题

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'

赞(0）回复(0）举报 2021-06-21

p3rjfoxz2#

它对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

赞(0）回复(0）举报 2021-06-20

我来回答

in-where子句更新表

2条答案

相关问题

热门标签

最新问答