mysql的双表查询更新数据问题。。。。。

[liwan123]

使用内链inner join来进行两个表的数据查询和更新，我有表phome_ecms_news和phome_enewsmember，表phome_ecms_news有字段id,userid,writer,smalltext,phome_enewsmember有字段userid,username。

现在我想更新phome_ecms_news的userid字段内容，当phome_ecms_news的writer=phome_enewsmember的username时，更新phome_ecms_news的userid内容=phome_enewsmember的userid，我这样写对吗？

update phome_ecms_news a1 inner join phome_enewsmember a2 using(writer) set a1.userid=a2.userid where a1.writer = a2.username

using(writer)用这个字段对吗？这两个表没有相同内容的字段，userid内容现在是不同的

[liwan123]

update phome_ecms_news a left join phome_enewsmember b on a.writer=b.username set a.userid=b.userid where a.writer = b.username我刚用了这个语句执行，好像执行超时了，郁闷

[wwwwb]

update phome_ecms_news a1 inner join phome_enewsmember a2 on a1.writer = a2.username set a1.userid=a2.userid where or update phome_ecms_news a1 , phome_enewsmember a2 set a1.userid=a2.userid where a1.writer = a2.username

[liwan123]

引用 2 楼 wwwwb 的回复:

update phome_ecms_news a1 inner join phome_enewsmember a2 on a1.writer = a2.username set a1.userid=a2.userid where or update phome_ecms_news a1 , phome_enewsmember a2 set a1.userid=a2.userid where a1.writer = a2.username

我的phome_ecms_news表有9万多条数据，能有高效的语句吗？我一执行就超时了

[wwwwb]

在writer、username上建立索引

[rucypli]

update phome_ecms_news a1 ， phome_enewsmember a2 set a1.userid=a2.userid where a1.writer = a2.username

[liwan123]

update phome_ecms_news a1 , phome_enewsmember a2 set a1.userid=a2.userid where a1.writer = a2.username and 1000<a1.id<1500用了这个语句，才几百条数据就超时了，我晕

引用 5 楼 rucypli 的回复:

update phome_ecms_news a1 ， phome_enewsmember a2 set a1.userid=a2.userid where a1.writer = a2.username

[liwan123]

update phome_ecms_news a1 , phome_enewsmember a2 set a1.userid=a2.userid where a1.writer = a2.username and a1.id<1500用这个语句倒是很快，但是用了上面的就慢的要命了

[wwwwb]

update phome_ecms_news a1,phome_enewsmember a2 set a1.userid=a2.userid where a1.writer = a2.username WHERE A1.ID>1000 AND a1.id<1500 在writer、ID 上建立索引 username 上建立索引