mysql多表关联更新

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