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 |
|
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