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
