mysql关联多表进行update更新操作
UPDATE Track
INNER JOIN MV
ON Track.trkid=MV.mvid
SET Track.is_show=MV.is_show
WHERE trkid<6
 
等同于
 
UPDATE Track,MV
SET Track.is_show=MV.is_show
WHERE Track.trkid=MV.mvid and trkid<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_4 fav

ON tag.atu_tag_id=fav.af_tag_id and 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_id and atu_user_id=af_user_id and af_content_id = 535522 and af_content_type=3 and af_user_id=374

group by af_user_id,af_tag_id) AS T1

ON tag.atu_tag_id=T1.af_tag_id and tag.atu_user_id=T1.af_user_id

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