在mysql中更新和选择同一张表问题 [英] update and select the same table problem in mysql

问题描述

您好，想运行这样的查询:

hi want to run a query like this:

UPDATE `stories` SET `position`=(select @i:=@i+1) 
WHERE `topic` IN 
    (SELECT `topic` FROM `stories` WHERE `newstype`='2' GROUP BY `topic`)

但目标和目的地是同一个表，mysql 不允许我运行它.我该如何实施?

but target and destination are the same table and mysql doesn't allow me running it. how can i implement it ?

推荐答案

你可以模拟内连接并只更新到第一个表，如

you can simulate inner join and update only to first table, like

set @pos:=0; 
update 
  stories a, 
  (select topic, @pos:=@pos+1 as new_position 
   from stories 
   where newstype=2 group by topic
  ) as b 
set a.position=b.new_position 
where a.topic=b.topic;

这篇关于在mysql中更新和选择同一张表问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！