mysql关联多表进行update更新操作UPDATE TrackINNER JOIN MVON Track.trkid=MV.mvidSETTrack.is_show=MV.is_showWHEREtrkid<6
等同于
UPDATE Track,MVSETTrack.is_show=MV.is_showWHERE Track.trkid=MV.mvid andtrkid<6
【扩展】根据结果集进行update更新操作原表信息表1：am_favorites_4

am_favorites_4表

af_user_id

af_tag_id

af_content_id

af_content_type

374

0

535522

3

374

0

535522

3

374

89

535522

3

表2：am_tag_user_4

am_tag_user_4表

atu_user_id

atu_tag_id

atu_num

374

0

9

374

89

9


更新结果目标表：am_tag_user_4

am_tag_user_4 表

atu_user_id

atu_tag_id

atu_num

374

0

7

374

89

8

UPDATE am_tag_user_4 tag

INNER JOIN am_favorites_4fav

ON tag.atu_tag_id=fav.af_tag_idand tag.atu_user_id=fav.af_user_id

INNER JOIN (SELECT af_user_id,af_tag_id,count(*)as cnt

FROM am_favorites_4,am_tag_user_4

where atu_tag_id=af_tag_idand atu_user_id=af_user_id andaf_content_id = 535522 and af_content_type=3 andaf_user_id=374

group by af_user_id,af_tag_id)AS T1

ON tag.atu_tag_id=T1.af_tag_idand tag.atu_user_id=T1.af_user_id

SET tag.atu_num=tag.atu_num-T1.cnt