在update语句中使用子查询

2019独角兽企业重金招聘Python工程师标准>>>

在update 中的 where 子句中使用子查询：

UPDATE mg_page_log as a  
SET  page_num=1 
WHERE id in( SELECT id  from mg_page_log WHERE id < 100 GROUP BY visit_id)

会报：

You can't specify target table 'a' for update in FROM clause 错误

所以正确的是：

UPDATE mg_page_log as a ,
( SELECT id  from mg_page_log WHERE id < 100 GROUP BY visit_id)as b 
SET  page_num=1 WHERE a.id = b.id

 

——————————————————————————

UPDATE customer as a ,customer0 as aa 
SET a.lastVisitTime = aa.lastFollowTime ,a.visitCount = 1 
WHERE a.id = aa.id AND a.phase=2 AND a.lastVisitTime = 0

 

转载于:https://my.oschina.net/u/2399303/blog/777727