表A有两个字段content_eid和content_version,并且在content_eid和content_version上面建立了唯一性索引,两个不能同时一样
表B有两个字段eid,rid,rid为自增字段
eid要插入到A表的content_eid
eid有重复数据,所以要用添加sid字段为标示字段(对应content_version).比如 eid:2 2 2 sid:0 1 2;eid:5 5 5 5 sid:0 1 2 3 我的想法是复制B表alter table B add column sid int(11);
create table C select * from Bupdate B
set sid=(select count(*) as cnt
from A join B ON A.EID=B.EID
WHERE A.RID<B.RID)但是提示错误,请大家指教一下谢谢
表B有两个字段eid,rid,rid为自增字段
eid要插入到A表的content_eid
eid有重复数据,所以要用添加sid字段为标示字段(对应content_version).比如 eid:2 2 2 sid:0 1 2;eid:5 5 5 5 sid:0 1 2 3 我的想法是复制B表alter table B add column sid int(11);
create table C select * from Bupdate B
set sid=(select count(*) as cnt
from A join B ON A.EID=B.EID
WHERE A.RID<B.RID)但是提示错误,请大家指教一下谢谢
update
B,A
set
B.sid=(select count(*) as cnt from A join B ON A.EID=B.EID WHERE A.RID<B.RID)
from A join B ON A.EID=B.EID
WHERE A.RID <B.RID) tt
set B.sid=tt.c
set b.sid = (select count(*) from A where a.eid=b.eid and a.rid<b.rid)
提示you can't specify target table
for update in from clause