update tab set number = (select count(*) from tab t where t.name =tab.name and t.idate<=tab.idate) 版本 mysql 4.1 mysql 5.0 请教执行上面sql语句报: 1093 you can't specify target table 'tab' for update in from clause
create table rr as select count(*) from tab t inner join tab b on t.name =b.name and t.idate<=b.idateupdate tab a inner join rr b on a.name =b.name set a.number =b.ma
or update tab c inner join (select count(*) from tab t inner join tab b on t.name =b.name and t.idate <=b.idate ) d on c.name =d.name set c.number =d.ma
正确的SQL语句如下。 update tab,(select a.name,count(*) as cnt from tab a inner join tab b on a.name =b.name and a.idate<=b.idate group by a.name) c set tab.number=c.cnt where tab.name=c.name
呵呵,标准的mysql限制,不允许在update里面使用包含主表的子查询。用3楼的方法吧
update tab c inner join (select t.name,count(*) as ma from tab t inner join tab b on t.name =b.name and t.idate <=b.idate group by t.name) d on c.name =d.name set c.number =d.ma
select count(*) from tab t
inner join tab b
on t.name =b.name and t.idate<=b.idateupdate tab a inner join rr b on a.name =b.name
set a.number =b.ma
update tab c inner join
(select count(*) from tab t
inner join tab b
on t.name =b.name and t.idate <=b.idate ) d
on c.name =d.name
set c.number =d.ma
update tab,(select a.name,count(*) as cnt
from tab a inner join tab b on a.name =b.name and a.idate<=b.idate
group by a.name) c
set tab.number=c.cnt
where tab.name=c.name
(select t.name,count(*) as ma from tab t
inner join tab b
on t.name =b.name and t.idate <=b.idate group by t.name) d
on c.name =d.name
set c.number =d.ma